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

- 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
  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"

for Value_Data
  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

Here our result:


  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

    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

  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!

  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

    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)

    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

    3. Can you post your dataflow json?

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

  4. This comment has been removed by the author.

    1. where do you define field of Type_Data_New?

    2. This comment has been removed by the author.

    3. yes, they are in Compute Expression node, but it need to compare or assign from a value or existing field to field defined in the Compute Expression node, not to define field in the SAQL


Page-level ad