Hi, I have a calendar table and a "Consultants" table.
In the Consultants table I have the consultants' IDs as well as other infos, the most important one is the HiringDate.
I need to create n "ExpandedCons" table where I take the dates from the calendar (only 2024) and it has to create a record for every consultant in every date, only if the CalendarDate is >= HiringDate
I tried with JOIN but it's quite different from sequel and it seems that If I do something like:
LOAD
Date ConsID
Resident Calendar WHERE Year(Date) = 2024; JOIN (Consultants)
LOAD
ConsID
HiringDate
Resident Consultants
Where Date >= HiringDate
It's not working because it can't find ConsID in Calendar (Calendar doesn't have column ConsID but I thought that it was like SQL where you can select the columns from the whole tables in the join.
A join itself should be possible but it would be only the first step because Qlik supports no conditions on joins - neither against the own table-data nor to the other table. This means at first came a cartesian join between the tables without a key-field and the next steps are reducing the data-set to the wanted sub-set.
Personally I would go another way by using an internal load-loop, for example with something like this:
LOAD ConsID, HiringDate, date(rangemax(makedate(2024), HiringDate) + iterno() - 1) as Date Resident Consultants while rangemax(makedate(2024), HiringDate) + iterno() - 1 <= today();
A join itself should be possible but it would be only the first step because Qlik supports no conditions on joins - neither against the own table-data nor to the other table. This means at first came a cartesian join between the tables without a key-field and the next steps are reducing the data-set to the wanted sub-set.
Personally I would go another way by using an internal load-loop, for example with something like this:
LOAD ConsID, HiringDate, date(rangemax(makedate(2024), HiringDate) + iterno() - 1) as Date Resident Consultants while rangemax(makedate(2024), HiringDate) + iterno() - 1 <= today();
Many thanks for the reply, the problem is that I have a column in the calendar table which I need to filter on (the column is called "working-day" and it's simply a 0 - 1 if the day is sat - sund or mond- frid, so I need the join with the calendar table
Not mandatory. The date-field from this expanded table and the calendar are associated and an appropriate selection or set analysis would transfer the selection state. In your case a simple sum([working-day]) should be enough for the most views.
