Filling in missing dates for multiple Categories in Qlik-Sense

[Ceb]

Hi,

I have read multiple posts related to handling missing dates. But I am not sure how to do it for my case over here.

Assume my below dataset.

Group	States	Date initiated	Total Sales
A	Fl	4-Jan-19	3
A	Fl	7-Jan-19	2
A	NY	3-Jan-19	2
A	MA	6-Jan-19	2
B	MA	11-Jan-19	1
B	MA	14-Jan-19	1
B	NH	4-Jan-19	1
B	NH	6-Jan-19	1

I have 2 groups and 4 different states. In the visualization I will create a master table with all this data and use filter to filter out programs and states.

In the above data-set I have missing dates for states. How should I go about filling the missing dates and even put zero in Total Sales for the missing dates. I have done it for a single group previously but this has multiple groups, so it is kind of confusing.

I need something like this.

Group	Program	Date initiated	Total Sales
A	Fl	1-Jan-19	0
A	Fl	2-Jan-19	0
A	Fl	3-Jan-19	0
A	Fl	4-Jan-19	3
A	Fl	5-Jan-19	0
A	Fl	6-Jan-19	0
A	Fl	7-Jan-19	2
A	Fl	8-Jan-19	0
A	Fl	9-Jan-19	0
A	Fl	10-Jan-19	0
A	NY	1-Jan-19	0
A	NY	2-Jan-19	0
A	NY	3-Jan-19	2
A	NY	4-Jan-19	0
A	NY	5-Jan-19	0
A	NY	6-Jan-19	0
A	NY	7-Jan-19	0
A	NY	8-Jan-19	0
A	NY	9-Jan-19	0
A	NY	10-Jan-19	0

And so on.....

I can do it SQL but filtering it out and then using a LEFT JOIN with a master calendar and then do a UNION of all the data but I was thinking if I can work something in Qlik-Sense itself.

Thanks

[Nicole-Smith · Accepted solution]

This load script looks to work:

/* Original data */
Data:
LOAD * INLINE [
    Group, States, Date initiated, Total Sales
    A, Fl, 2019-01-04, 3
    A, Fl, 2019-01-07, 2
    A, NY, 2019-01-03, 2
];

/* Create a calendar */
LET vMinDate = MakeDate(2019, 1, 1);
LET vMaxDate = MakeDate(2019,1,10);

AllDates:
LOAD Date('$(vMinDate)' + IterNo() - 1) AS [Date initiated]
AutoGenerate 1
WHILE '$(vMinDate)' + IterNo() - 1 <= '$(vMaxDate)';

/* Get list of Groups and States */
Temp:
LOAD DISTINCT Group, States
RESIDENT Data;

/* Adding all dates */
LEFT JOIN (Temp)
LOAD [Date initiated]
RESIDENT AllDates;

/* Adding values */
LEFT JOIN (Temp)
LOAD *
RESIDENT Data;

DROP TABLES Data, AllDates;

/* Replacing nulls with zeroes */
NullAsValue [Total Sales];
Set NullValue = 0;

Final:
NOCONCATENATE LOAD *
RESIDENT Temp;

DROP TABLE Temp;

It returns this table:

Group	States	Date initiated	Total Sales
A	Fl	1/1/2019	0
A	Fl	1/2/2019	0
A	Fl	1/3/2019	0
A	Fl	1/4/2019	3
A	Fl	1/5/2019	0
A	Fl	1/6/2019	0
A	Fl	1/7/2019	2
A	Fl	1/8/2019	0
A	Fl	1/9/2019	0
A	Fl	1/10/2019	0
A	NY	1/1/2019	0
A	NY	1/2/2019	0
A	NY	1/3/2019	2
A	NY	1/4/2019	0
A	NY	1/5/2019	0
A	NY	1/6/2019	0
A	NY	1/7/2019	0
A	NY	1/8/2019	0
A	NY	1/9/2019	0
A	NY	1/10/2019	0

[Ceb]

This works great!!!

Thanks a lot