Use this code in your script and you will get your desired results in UI without changing any expression.
Cohort&'|'&"Age" as ExistField //Created this field to use exists funtion
FROM [lib://Temp downloads/Cohort for ex.xlsx]
(ooxml, embedded labels, table is Sheet1);
Load Distinct Cohort Resident CohortForEx;
Load Distinct "Age",0 as Amount Resident CohortForEx;
// Took all possible combinations of Age and Cohort with zero amount
LOAD * Resident Temp where not Exists(ExistField,Cohort&'|'&"Age");
//Added only those combinations of Cohort and Age that does not exist in your data with Amount as 0
Drop Table Temp;
Drop Field ExistField;
Thank you for the quick response, however there are a few issues with this approach:
- It will significantly increase application load time as my real data set contains more than 700K...
- Although technically it shows correct total amounts, it is incorrect in terms of Cohort analysis - the Q4-2016 group has not reached age 2 yet (for example, for customers who joined the company in Q4-2016 this is only the first Q=age 0 with the company) so I cannot show amount of 14 at age 2 for Q4-2016.
The desired output would look like this:
Cohort by age test.qvf 176.0 K