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:





22 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. Hello , Please can anyone answer this , I have got a reverse situation , I have table 2 currently and I want to convert it to table1 . How to do that, please let me know ?

      Delete
    3. you can use append node to combine data

      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
  4. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. where do you define field of Type_Data_New?

      Delete
    2. This comment has been removed by the author.

      Delete
    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

      Delete
  5. Hi Joahn. So for the ceData Compute expression, I am getting this error: "Something went wrong while executing the computeAllData node: invalid field expression for field 'Type_Data': Make sure the "Type_Data_1" identifier exists and is spelled correctly without spaces. (02K35000000LzW0EAK_03C35000000vQnIEAU)". Am I supposed to substitute "Type_Data_1" with field name?

    ReplyDelete
    Replies
    1. Type_Data_1 should be compute field defined in ce node

      Delete
  6. Hi Joahn.

    So for the 'Question_1' Compute expression, am getting this error: "Something went wrong while executing the Question_1 node: invalid field expression for field 'Value_Question_1' [error category: USER]: Make sure the "Q_15" identifier exists and is spelled correctly without spaces. (02K8E000000XGNCUA4_03C8E000001BM54UAG)"

    Please find below the SAQL Expressions for both the compute fields('Type_Question_1' and 'Value_Question_1') created in Question_1 Compute expresion node:

    Type_Question_1 = "Q15"
    Value_Question_1 = Q_15

    Please let me know if I need to make any changes in the SAQL expressions for both the compute fields or any other customizations is required.

    ReplyDelete
    Replies
    1. try to make Q_15 and Q15 similar, and try again

      Delete

Page-level ad