topic Generating Missing months with zero value in Qliksense Script in QlikView

Generating Missing months with zero value in Qliksense Script

hitha1512 — Fri, 17 Feb 2023 10:01:06 GMT

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.

Re: Generating Missing months with zero value

abhijitnalekar — Fri, 17 Feb 2023 09:51:10 GMT

you can use number formatting for achieve the same.

Re: Generating Missing months with zero value

hitha1512 — Fri, 17 Feb 2023 14:18:10 GMT

Hello,

It does not work in Qliksense. Does it ?

Re: Generating Missing months with zero value in Qliksense Script

ss0425 — Fri, 17 Feb 2023 14:40:59 GMT

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.

Re: Generating Missing months with zero value in Qliksense Script

edwin — Fri, 17 Feb 2023 21:31:34 GMT

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;

Re: Generating Missing months with zero value in Qliksense Script

sidhiq91 — Sat, 18 Feb 2023 03:14:28 GMT

@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