Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all. I'm trying a new solution for a problem I posted here. I am now trying instead to combine separate tables in the UI, where one of the tables has an incomplete date set.
I want to generate the DesiredUITable below, where across the dimensions of Person and MonthEnd, if there is a corresponding FactMonthEnd I get the Fact value, else I get 0 or null.
My attempt below right is if(FactMonthEnd=MonthEnd,Fact). This generates excess null values on the rows where there is a Fact.
Any suggestions how I can get to the DesiredUITable below? Thanks in advance.
DateTable: | FactTable: | DesiredUITable: | |||||||
Person | MonthEnd | Person | FactMonthEnd | Fact | Person | MonthEnd | Desired Expression | ||
1 | 31.01.2018 | 1 | 31.01.2018 | 10 | 1 | 31.01.2018 | 10 | ||
1 | 28.02.2018 | 1 | 31.03.2018 | 10 | 1 | 28.02.2018 | 0 or null | ||
1 | 31.03.2018 | 1 | 31.05.2018 | 40 | 1 | 31.03.2018 | 10 | ||
1 | 30.04.2018 | 2 | 28.02.2018 | 20 | 1 | 30.04.2018 | 0 or null | ||
1 | 31.05.2018 | 2 | 30.04.2018 | 20 | 1 | 31.05.2018 | 40 | ||
1 | 30.06.2018 | 2 | 30.06.2018 | 10 | 1 | 30.06.2018 | 0 or null | ||
2 | 31.01.2018 | 2 | 31.01.2018 | 0 or null | |||||
2 | 28.02.2018 | 2 | 28.02.2018 | 20 | |||||
2 | 31.03.2018 | 2 | 31.03.2018 | 0 or null | |||||
2 | 30.04.2018 | 2 | 30.04.2018 | 20 | |||||
2 | 31.05.2018 | 2 | 31.05.2018 | 0 or null | |||||
2 | 30.06.2018 | 2 | 30.06.2018 | 10 | |||||
Person | MonthEnd | if(FactMonthEnd=MonthEnd,Fact) | |||||||
1 | 31.01.2018 | 10 | |||||||
1 | 31.01.2018 | null (unwanted) | |||||||
1 | 28.02.2018 | null (desired) | |||||||
1 | 31.03.2018 | 10 | |||||||
1 | 31.03.2018 | null (unwanted) | |||||||
1 | 30.04.2018 | null (desired) | |||||||
1 | 31.05.2018 | 40 | |||||||
1 | 31.05.2018 | null (unwanted) | |||||||
1 | 30.06.2018 | null (desired) | |||||||
2 | 31.01.2018 | null (desired) | |||||||
2 | 28.02.2018 | 20 | |||||||
2 | 28.02.2018 | null (unwanted) | |||||||
2 | 31.03.2018 | null (desired) | |||||||
2 | 30.04.2018 | 20 | |||||||
2 | 30.04.2018 | null (unwanted) | |||||||
2 | 31.05.2018 | null (desired) | |||||||
2 | 30.06.2018 | 10 | |||||||
2 | 30.06.2018 | null (unwanted) |