How to change the format of my data?

[xvxt006]

Hi,

we have data that i am getting report using addcols to combine the data and using transpose to get the data in the below format. if anyone wants the query that i am using, i can send it

Measure value
number of subscriptions today 10
number of subscriptions Lastweek 5
Delta in subscriptions % 100%
Revenue today $ 100
Revenue Lastweek $ 50

Delta in Revenue 100%
Requests today 200
Requests LastWeek 100
Delta in Requests 100%

I want the data in the below format -

Measure Today LastWeek Delta

Subscriptions 10 5 100
Revenue $ 100 $ 50 100
Requests 200 100 100

How can this be done?

[martin_mueller]

As naïve postprocessing, you could do this:

... | eval Today = case(match(Measure, "(?i)today")) | eval LastWeek = case(match(Measure, "(?i)lastweek")) | eval Delta = case(match(Measure, "(?i)delta")) | eval Measure = case(match(Measure, "(?i)subscriptions"), "Subscriptions", match(Measure, "(?i)revenue"), "Revenue", match(Measure, "(?i)requests"), "Requests") | stats values(Today) as Today values(LastWeek) as LastWeek values(Delta) as Delta by Measure

However, that's fairly ugly... and I agree with Lisa, there's probably a nicer way to produce the directly data in your query.

[lguinn2]

Please post the query, and it will be simple for us to respond.