Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
MikeH1983
New Contributor III

QlikSense: How to combine read an incomplete date table into an Expression

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:  
PersonMonthEnd PersonFactMonthEndFact PersonMonthEndDesired Expression
131.01.2018 131.01.201810 131.01.201810
128.02.2018 131.03.201810 128.02.20180 or null
131.03.2018 131.05.201840 131.03.201810
130.04.2018 228.02.201820 130.04.20180 or null
131.05.2018 230.04.201820 131.05.201840
130.06.2018 230.06.201810 130.06.20180 or null
231.01.2018     231.01.20180 or null
228.02.2018     228.02.201820
231.03.2018     231.03.20180 or null
230.04.2018     230.04.201820
231.05.2018     231.05.20180 or null
230.06.2018     230.06.201810
          
          
          
          
       PersonMonthEndif(FactMonthEnd=MonthEnd,Fact)
       131.01.201810
       131.01.2018null (unwanted)
       128.02.2018null (desired)
       131.03.201810
       131.03.2018null (unwanted)
       130.04.2018null (desired)
       131.05.201840
       131.05.2018null (unwanted)
       130.06.2018null (desired)
       231.01.2018null (desired)
       228.02.201820
       228.02.2018null (unwanted)
       231.03.2018null (desired)
       230.04.201820
       230.04.2018null (unwanted)
       231.05.2018null (desired)
       230.06.201810
       230.06.2018null (unwanted)