Generating Missing months with zero value in Qliksense Script

[hitha1512]

Hi Team,

After all my data modelling I have data that looks like below. Every Item for specific month will show downtime. I would like to generate the missing months for every Item with 0 value .

Final Result got:

Item	Red	Blue	Green	Yellow
July 2022			9.22	0.43
Aug 2022	0.33
Sep 2022		1.22
October 2022				6
Nov 2022	4.5
Dec 2022
January 2023			1
Feb 2023

Result expected :

Item	Red	Blue	Green	Yellow
Jul 2022	0	0	9.22	0.43
Aug 2022	0.33	0	0	0
Sep 2022	0	1.22	0	0
Oct 2022	0	0	0	6
Nov 2022	4.5	0	0	0
Dec 2022	0	0	0	0
Jan 2023	0	0	1	0
Feb 2023	0	0	0	0

Any help would be appreciated. Thank you.

[abhijitnalekar]

you can use number formatting for achieve the same. 

[hitha1512]

Hello,

It does not work in Qliksense. Does it ?

[ss0425]

In QlikSense, you can generate missing months with zero value in the script using a combination of the Calendar function and Left Join. Here's an example script that generates missing months with zero value:

// Generate a calendar table with all dates from min and max date in your data
Calendar:
Load
    Date($(vMinDate) + IterNo() - 1) as Date
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

// Load your data table
YourData:
Load
    Date,
    Value
From YourDataSource;

// Join the data table with the calendar table to get all dates, including missing ones
Left Join (YourData)
Load
    Date
Resident Calendar;

// Replace all NULL values with 0 to indicate missing months
YourData_Final:
Load
    Date,
    If(IsNull(Value), 0, Value) as Value
Resident YourData;

In this script, we first generate a Calendar table using the Calendar function, which creates a table with all dates between the minimum and maximum dates in your data. We then load your data into a table called YourData.

Next, we perform a Left Join to combine the Calendar table with the YourData table, which will add missing dates to the YourData table with null values.

Finally, we create a new table called YourData_Final, where we replace all NULL values with 0 using the IsNull and If functions.

By using this script, you can generate a table that includes all months, including the ones with zero value, which is useful for creating visualizations and analyzing trends over time.

[edwin]

starting data:

then try this:

NoConcatenate
newData:
load Item,
if(isnull(Red),0,Red) as Red,
if(isnull(Blue),0,Blue) as Blue,
if(isnull(Green),0,Green) as Green,
if(isnull(Yellow),0,Yellow) as Yellow
resident data;
drop table data;

[sidhiq91]

@hitha1512  Please see the code below:

NoConcatenate
Temp:
Load left(SubField(Item,' ',1),3) as Month,
SubField(Item,' ',2) as Year,
Red,
Blue,
Green,
Yellow

inline [
Item, Red, Blue, Green, Yellow
July 2022,, 9.22, 0.43,
Aug 2022, 0.33,,,
Sep 2022, ,1.22,,
October 2022,,,, 6
Nov 2022, 4.5,,,
Dec 2022,
January 2023,,, 1,
Feb 2023,,,,
];

NoConcatenate
Temp1:
load Month&' '&Year as Item,
if(isnull(emptyisnull(Red)),0,Red) as Red,
if(isnull(emptyisnull(Blue)),0,Blue) as Blue,
if(isnull(emptyisnull(Green)),0,Green) as Green,
if(isnull(emptyisnull(Yellow)),0,Yellow) as Yellow
Resident Temp;

Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept as a solution