Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
user467341
Creator II
Creator II

Script not loading missing date correctly in model

Hi.

I am trying to fill up missing dates in my data model. Below is the INITIAL sample table:

Date Item Value
1/1/2023 Cat 43
1/1/2023 Dog 41
2/1/2023 Cat 13
4/1/2023 Cat 57
4/1/2023 Dog 83
4/1/2023 Mouse 62
5/1/2023 Cat 62
5/1/2023 Dog 41

In data load editor, I used the following:

TableA:
LOAD "DateNew",
   "Item",
   "Value",
SQL SELECT "DateNew",
   "Item",
   "Value",
FROM DatabaseSQL;

JOIN(TableA)

 Load Date(MinDate + IterNo() -1 ) AS DateNew While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load
    Min(DateNew) AS MinDate,
    Max(DateNew) AS MaxDate
RESIDENT TableA;

Using this script, I created a straight table and got this result:

Date Item Value
1/1/2023 Cat 43
1/1/2023 Dog 41
1/1/2023 - -
2/1/2023 Cat 13
2/1/2023 - -
3/1/2023 - -
4/1/2023 Cat 57
4/1/2023 Dog 83
4/1/2023 Mouse 62
4/1/2023 - -
5/1/2023 Cat 62
5/1/2023 Dog 41
5/1/2023 - -
6/1/2023 - -

However, the table is wrong. You can see that there is a '-' EVERYDAY for "Item" and "Value". I thought the script should return this output instead:

Date Item Value
1/1/2023 Cat 43
1/1/2023 Dog 41
2/1/2023 Cat 13
3/1/2023 - -
4/1/2023 Cat 57
4/1/2023 Dog 83
4/1/2023 Mouse 62
5/1/2023 Cat 62
5/1/2023 Dog 41
6/1/2023 - -

Where it will only fill in the missing date (in this case, 3/1, 6/1) with '-'. 

Why is the script creating a '-' everyday instead of just filling the missing dates?

Labels (4)
1 Reply
RafaelBarrios
Partner - Specialist
Partner - Specialist

Hi @user467341 

try not joining the tables.

you will have a separated table that will work as a master calendar, this is very usefull for not repeating dates, months, years values for every record.

 

Take a look here

https://www.youtube.com/watch?v=ZW1gJAgBP2k&t=438s

 

hope it helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!