Pages

Sunday, September 29, 2019

Einstein Analytics: Transpose data from columns to rows

In the table-1 below; Data-1, Data-2, and Data-3 are stored as individual columns.



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:





12 comments:

  1. 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.

    Question 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

    ReplyDelete
    Replies
    1. Hi Miss C,
      You 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

      Delete
  2. 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")

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

    ReplyDelete
  3. Hi Johan, After running the dataflow we will get the data in Table2 format, right.
    So 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

    ReplyDelete
    Replies
    1. - Type_Data_1 = "Data-1"
      - 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)

      Delete
    2. - Type_Data_1 = "Data-1"
      - Value_Data_1 = Data_1

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

      Delete
    3. Can you post your dataflow json?

      Delete
    4. Thank You Johan, I got it corrected, now its fine.
      Thanks a lot.

      Delete

Page-level ad