Filling in missing dates for multiple Categories in Qlik-Sense
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.
/* 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;
/* 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;
