Transposing data where two columns are fixed

[bismsit29]

HI All,

I am struggling with a query where i have made the data like the following

Type	_time	Store	Counts
Type1	22/06/2020 11:00	Store1	10
Type1	22/06/2020 11:00	Store2	20
Type1	22/06/2020 11:00	Store3	30
Type2	22/06/2020 11:00	Store1	100
Type2	22/06/2020 11:00	Store2	200
Type2	22/06/2020 11:00	Store3	300

And I need it to be like the below. Any help on this please ?

Type	_time	Store1	Store2	Store3
Type1	22/06/2020 11:00	10	20	30
Type2	22/06/2020 11:00	100	200	300

[manjunathmeti]

@bismsit29 , Try this:

| strcat Type "-" _time column
| xyseries column, Store, Counts
| eval column=split(column, "-"), Type=mvindex(column, 0), _time=mvindex(column, 1)
| fields Type, _time, Store*

[dmarling]

Without knowing the underlying query that is generating the table you provided here's the way you can get to what you want from that table. There is a more efficient way of doing this directly from underlying data if you can provide what that is.  If not here is how you can generate the desired table from the table you have.  Refer to lines 13-17.  The first 12 lines are me recreating the table you provided as an example.

| makeresults
| eval Data="Type1|22/06/2020 11:00|Store1|10
Type1|22/06/2020 11:00|Store2|20
Type1|22/06/2020 11:00|Store3|30
Type2|22/06/2020 11:00|Store1|100
Type2|22/06/2020 11:00|Store2|200
Type2|22/06/2020 11:00|Store3|300"
| makemv tokenizer="(?<Data>[^\n]+)" Data
| mvexpand Data
| rex field=Data "^(?<Type>[^\|]+)\|(?<time>[^\|]+)\|(?<Store>[^\|]+)\|(?<Counts>[^\e]+)"
| eval _time=strptime(time, "%d/%m/%Y %H:%M")
| table Type _time Store Counts
| eval ClownCar=Type."|"._time
| chart values(Counts) as Counts over ClownCar by Store
| rex field=ClownCar "(?<Type>[^\|]+)\|(?<time>[^\e]+)"
| eval _time=time
| table Type _time Store*