creating timechart grouped data tables

[stephenreece78]

hi all, 

newbee question here but i can't seem to find an answer.

I am trying to create a timechart table grouped table that has product as the parent and error codes as the child.  

I can't seem to get any sort of multiple column grouping where i can have separate calculations for each column variant.

so the structure would losely be:

                                     Product1                                                                                                                                Product2

               200         201       300       ......and so on                           200         201       300       303         ......and so on

date1      34             23      22                                                                 43           23         76         23
date2        2             43        45

.....and so on.

it would be fine to coalesce the prod and error code , but i can figure that out either i.e. 200Prod1    201Prod1     200Prod2    201Prod2

Is this something that is logically doable?

[yuanliu · Accepted solution]

You may want to elaborate your input so others can have some idea about what you are trying to achieve.

Assuming an input like the following:

_time	code	product
2021-05-20 08:42:43	200	prod1
2021-05-20 09:42:43	200	prod2
2021-05-20 10:42:43	200	prod1
2021-05-20 11:42:43	203	prod2
2021-05-20 12:42:43	200	prod2
2021-05-20 13:42:43	203	prod1
2021-05-20 14:42:43	202	prod2
...

which is simulated with the following

| makeresults count=30
| streamstats count
| eval _time = _time + count * 3600, product = if(random() % 2 == 0, "prod2", "prod1"), code = 200 + random() % 5
| fields - count

 There are several ways to obtain your desired outcome.  The simplest is to use coalesced headers

| eval code_prod = code . product
| timechart span=1d count by code_prod

This will give you something like

_time	200prod1	200prod2	201prod1	201prod2	202prod1	202prod2	203prod1	203prod2	204prod1	204prod2
2021-05-19	0	0	0	0	0	0	0	0	0	0
2021-05-20	2	2	3	0	2	2	1	1	2	0
2021-05-21	1	3	0	2	2	1	2	3	1	1

As timechart, the visual will not be very easy to grasp.

When I really, really want split charts, I have used the following technique. (Well, I use it mostly not for split charts.) Bewarned: things get ugly really fast when you have many different return codes.

| eval c200 = if(code == 200, 1, 0), c201 = if(code == 201, 1, 0), c202 = if(code == 202, 1, 0), c203 = if(code == 203, 1, 0), c204 = if(code == 204, 1, 0)
| timechart span=1d sum(c200) as 200 sum(c201) as 201 sum(c202) as 202 sum(c203) as 203 sum(c204) as 204 by product

The results table looks something like this

_time	200: prod1	200: prod2	201: prod1	201: prod2	202: prod1	202: prod2	203: prod1	203: prod2	204: prod1	204: prod2
2021-05-19
2021-05-20	1	1	0	0	1	3	3	2	2	2
2021-05-21	1	4	2	2	1	1	1	2	1	0

At first glance, this is not very different from the above.  But because the seemingly coalesced header is generated by timechart itself, timechart has a way to split them, called "trellis".  In "Visualization", if you check "Use Trellis Layout" under "Trellis", you can get graphic charts like this. ("Split by" in this example is product; you can also split by code.)

As I mentioned, this works when you have only a handful of codes to deal with.

[stephenreece78]

thanks for the quick reply.

i should have mentioned the table may well be exported and used in other applications so the first option you showed for output is ideal as it can be imported into other apps and interrogated easily