Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Derek_T
Contributor III
Contributor III

Dynamic joins - how would you solve this ?

I have 2 tables (see pictures below): Date, BirthDay that I'm combining to create the third one BirthDayCount using joins on the "Date" field in the script.

I need the "CumulBday" column, the issue is, the table is generated at load time and is unaffected by the user selection. So when a user filter for "Bob" the BirthDayCount table does not change and the "CumulBday" column still contains all the values.

Even if I were to include a variable in the script, the variable would only affect the table at load time, but would not refresh every time the user change the selection.

Is there a way to handle the joins and build the table right into the "Sheet" section of the app ?

Derek_T_0-1667491107051.png

 




6 Replies
edwin
Master II
Master II

one way is to add a bridge between your birthdates and the calendar.  the idea is to create an association between the calendar and the birthdates when a name is selected only the dates you are interested in are selectable and it will be range and not just the exact equals.  for example, if your calendar starts from 2000 to 2022 and you select a name with birthdate = 1/1/2020 - all the dates from  1/1/2020  to 2022 are selectable and therefore reportable.  the simplest part is cumulative.  since all the dates are associated, you simply count the names associated to each calendar date - this means your expression will be super fast.  what makes it complicated is now you want to report prior years where there are no birthdates yet, you then need a backward looking association.  y will then have two types of dates one that is for cumulative calculation that starts from the first birthdate selected going up to whatever date you want and a prior date that starts from the first date in your calendar going to your last birthdate.  heres my code:

 

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-CA';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';


NoConcatenate
Births:
load * inline [
Name, BirthDate
bob, 2022-01-20
cindy, 2021-03-22
megan, 2021-03-24
megan2, 2021-03-24
];

NoConcatenate
Dates:
load date(today()-iterno()+1) as Date
while date(today()-iterno()+1)>=date('2021-01-01');
load 1 AutoGenerate(1);

NoConcatenate
tmpBirthdays:
load distinct BirthDate resident Births;

outer join (tmpBirthdays)
load Date resident Dates;

NoConcatenate
Birthdays:
load BirthDate, Date, if(BirthDate=Date,1,0) as ExactDate, 'Cumulative' as type resident tmpBirthdays
where Date>=BirthDate;

concatenate(Birthdays)
load BirthDate, Date, if(BirthDate=Date,1,0) as ExactDate, 'Prior' as type resident tmpBirthdays
where Date<=BirthDate;

drop table tmpBirthdays;
edwin
Master II
Master II

this is how the DM looks like:

edwin_0-1667561086346.png

 

edwin
Master II
Master II

for the UI, i created a variable 

edwin_1-1667561209880.png


i didnt want to display the whole calendar - only up to the last birthdate.  this is for cumulative:

edwin_2-1667561334674.pngedwin_3-1667561376748.png

notice where it starts and ends - based on selected names.

this includes teh start of the calendar and individual birthdates my calendars starts 1/1/2021:

edwin_4-1667561517377.png

this is the first birthdate:

edwin_5-1667561662695.png

 



edwin
Master II
Master II

cumulative:
count({<type={'Cumulative'}, Date={"<=$(maxDate)"}>}Name)

the expression that counts exact birthdates:
=sum({<type={'Prior'}>}ExactDate)

you need to set these:

edwin_6-1667561838086.png

 

edwin
Master II
Master II

this solution leverages the associative engine which is what you really are buying.  you can implement a complicated expression for sure

Derek_T
Contributor III
Contributor III
Author

Thanks Edwin unfortunately it did not work for me. The info was available in another table I was able to get it without doing all of this.