topic Re: Dynamic joins - how would you solve this ? in QlikView

Dynamic joins - how would you solve this ?

Derek_T — Thu, 03 Nov 2022 15:59:29 GMT

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 ?

Re: Dynamic joins - how would you solve this ?

edwin — Fri, 04 Nov 2022 11:22:10 GMT

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;

Re: Dynamic joins - how would you solve this ?

edwin — Fri, 04 Nov 2022 11:24:55 GMT

this is how the DM looks like:

Re: Dynamic joins - how would you solve this ?

edwin — Fri, 04 Nov 2022 11:34:36 GMT

for the UI, i created a variable

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

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:

this is the first birthdate:

Re: Dynamic joins - how would you solve this ?

edwin — Fri, 04 Nov 2022 11:37:25 GMT

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

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

you need to set these:

Re: Dynamic joins - how would you solve this ?

edwin — Fri, 04 Nov 2022 11:38:42 GMT

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

Re: Dynamic joins - how would you solve this ?

Derek_T — Mon, 07 Nov 2022 19:17:55 GMT

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.