Sunday, September 29, 2019

Einstein Analytics: Transpose multi columns to single column

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:

No comments:

Post a Comment

Page-level ad