Hi @ASK -
Currently QuickSight does not allow you to perform a union in data prep. The easiest approach would be to union this data in your ETL.
Assuming that you want to do this 100% in QuickSight, you can use this work around.
Step 1 - Create a modified calendar table to serve as your union.
This will be all possible dates plus a column indicating Table1 (Creation) or Table 2 (Deletion)
Step 2 & 3 - Create a calculated field in Table1 and Table2 datasets.
Table
Step 4 - Join Table1 and Table2 to your union dataset using your Table calculated field and date.
Step 5 - Create calculated field to count the creation/deletion. Note: you will take the same approach for any value fields you have.
c_count
ifelse(isnull(Table),{Table[table1]},Table)
Result:
Hi @robdhondt,
Thanks for your help!
Small correction in my question, input is already a prepared dataset and have lot of other dependent calculations and visuals on top of the dataset, now I need to create a visual using the below dataset.
Hi @ASK -
Your first question was regarding pivot/union and your second is looking to unpivot. The good news is that unpivot in data prep is “coming soon” see related question: How to unpivot columns in QuickSight? .
Here is a workaround that might get you by in the meantime.
Step 1 - Create a helper dataset “unpivot table”.
This is modified calendar table.
Step 2 - Create a calculated field in your dataset as a link.
Step 3 - Join your unpivot dataset helper with your dataset using the link field.
Step 4 - Create calculated fields to switch between the unpivoted columns
c_count
ifelse(Action='Creation' AND Date = {Creation Date},1,ifelse(Action='Deletion' AND Date = {Deletion Date},1,0))
Result:
Is it possible to make joins using calculated field? I’m not able to do it.
I have the same issue, not able to find the added link calculated field in the join list