Using Chart over two fields grouped by time

[kabiraj]

Hi Guys,

I am trying to pull up a table containing Time, Channel & Popularity as fields.

I am using : chart useother=f max(position) over Channel by Time

Above gives me only Channel & Time as fields in the table but i want Channel, Popularity & Time as fields.

I tried : chart useother=f max(position) over Channel Popularity by Time

But failed as it showed a syntax error.

Below is a sample of result

Channel 03-Nov-15 04-Nov-15
Watch 27 29
Universal 14 15
TLC HD 54 64
TLC 21 20

I want Popularity to be added besides the Channel field

Any Ideas?

[woodcock · Accepted solution]

Like this:

... | eval Channel_and_Popularity = Channel . ":" . Popularity | chart useother=f max(position) OVER Channel_and_Popularity BY Time

[kabiraj]

Hi Woodcock! Thanks for your prompt response but i want Channel & Popularity as two different fields instead of appending it into one delimited by ":".

Below is the example :

Channel Name Popularity 04-Nov-15 05-Nov-15
1 Sky Movies Premiere 1 78 74
2 Sky Movies Showcase 2 82 71
3 Sky Movies Greats 3 80 72
4 Sky Movies Disney 4 77 66
5 Sky Movies Family 5 81 75
6 Sky Movies Action & Adventure 6 79 60

Any other suggestions will be highly appreciated. 🙂

[woodcock]

Like this:

 ... | eval = Channel . ":" . Popularity | chart useother=f max(position) OVER Channel_and_Popularity BY Time | rex field=Channel_and_Popularity "(?<Channel >[^:]+):(?<Popularity >[^:]+)" | fields Channel Popularity *-*

[kabiraj]

Works like a Charm! Thank you! I think the Combination & splitting of fields and *-* did the trick. Thanks for sharing your expertise.
Just wanted to ask can we show N number of fields in the table just by giving *-* in the query?

[woodcock]

Yes, but they must be 1-to-1 (In other words each value deterministically maps to a single value in each of the joined fields).