Here is what we can get from Table-1

But, we can't have a total of A, B, and C as

**a single bar chart**for easy comparison, the easiest option is to change the dataset into Table-2.

__Solution__: use dataflow to expand the rows and create new fields with combine values

Notes:

- ceData1,ceData2,ceData3 contain 2 compute fields: Type and Value

- Type_Data_1 =

*"Data-1"*

- Value_Data_1 =

*Data_1*

- same goes for ceData2 and ceData3

- In appendAllData,

*select*"Allow disjoint schema"

- ceData also contain compute field Type and Value, and using Case in the formula

for Type_Data

*case*

*when Type_Data_1 is not null then "Data-1"*

*when Type_Data_2 is not null then "Data-2"*

*when Type_Data_3 is not null then "Data-3"*

*end*

for Value_Data

*case*

*when Type_Data_1 is not null then Data_1*

*when Type_Data_2 is not null then Data_2*

*when Type_Data_3 is not null then Data_3*

*end*

Here our result:

Great post, came in handy today was able to follow the method to transpose a revenue dataset that had monthly values column by column and not easily viewed as a time series.

ReplyDeleteQuestion around the last compute 'ceData' using Cases, what is the purpose of this since the first 3 computes would have populated the type & values already?

Thanks! C

to combine into new fields

DeleteHi Miss C,

DeleteYou use the same logic for ceData1 node as given above by Johan, For ceData1 node I used two compute exp field both text type, 1st one for Type, 2nd for value. But I am getting errors.

Can you please help me on this

Regards

Pranit

Awesome post! It helped me a lot while I was transposing a dataset with year and month columns! (I had 12 month columns and had to transpose into one as "Date")

ReplyDeleteYour posts are always a big help, thank you so much!

WonJeung

WonJeung, glad to hear it help #Ohana

DeleteHi Johan, After running the dataflow we will get the data in Table2 format, right.

ReplyDeleteSo what i understood is , 1] We have data in Table 1 format

2] We create Dataflow from Table 1 format data

3] Dataflow result is Table2

Am i correct Johan.

Thank You

yes, that's correct

Delete- Type_Data_1 = "Data-1"

Delete- Value_Data_1 = Data_1

Hi John but i am getting error at ceData1 node, though i am using same data as yours

My dataflow is not building.

Something went wrong while executing the CEData1 node: invalid field expression for field 'CEData1_2': Make sure the "Data" identifier exists and is spelled correctly without spaces. (02KB0000000P3frMAC_03CB0000002OG2EMAW)

- Type_Data_1 = "Data-1"

Delete- Value_Data_1 = Data_1

I am using these statements in my compute expression, in two different text fields

Can you post your dataflow json?

DeleteThank You Johan, I got it corrected, now its fine.

DeleteThanks a lot.

Great!

Delete