Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Federicofkt
Contributor III
Contributor III

Expand table with records from calendar

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.

 

Thanks!

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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();

 

View solution in original post

3 Replies
marcus_sommer

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();

 

Federicofkt
Contributor III
Contributor III
Author

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

marcus_sommer

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.