Script not loading missing date correctly in model

[user467341]

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?

[RafaelBarrios]

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!