topic Re: Populate Missing Data for all Materials in App Development

Populate Missing Data for all Materials

gauravs275 — Thu, 11 Nov 2021 09:27:04 GMT

Hello,

In my datasource, there are materials against which the month, year and stock are visible. In this datasource, if there is no change in stock over a particular month, then no new row is created for that missing month.

The smaller table shows how the datasource looks like. The larger table is how it should look like. If a month is missing, I need to replicate the data from the previous month to the current month. Like Feb data is used as the value for the missing Mar month.

PS: This same material can have the same issue across multiple years and regions.

Material	Region	Month	Year	Stock
1111111	EMEA	1	2021	10000
1111111	EMEA	2	2021	23000
1111111	EMEA	4	2021	19000
1111111	EMEA	5	2021	2000
1111111	EMEA	7	2021	14000

Material	Region	Month	Year	Stock
1111111	EMEA	1	2021	10000
1111111	EMEA	2	2021	23000
1111111	EMEA	3	2021	23000
1111111	EMEA	4	2021	19000
1111111	EMEA	5	2021	2000
1111111	EMEA	6	2021	2000
1111111	EMEA	7	2021	14000

Thanks.

Re: Populate Missing Data for all Materials

Or — Thu, 11 Nov 2021 09:48:00 GMT

This is a fairly common question - have a look at Henric's great explanation for it here:

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/1470637

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

Re: Populate Missing Data for all Materials

Vegar — Thu, 11 Nov 2021 09:59:05 GMT

Try to do something like this:

Let vL.startyear =2021; Let vL.noOfMonths = 24; TMP_Fulltrans: LOAD *, monthname(MakeDate(Year, Month)) as YearMonth, ; LOAD MOD( iterno()-1,12)+1 as Month, $(vL.startyear)+Div(iterno()-0.5,12) as Year AutoGenerate 1 While iterno() <= $(vL.noOfMonths) ; LEFT JOIN LOAD * INLINE [ Material, Region, Month, Year, Stock 1111111, EMEA, 1, 2021, 10000 1111111, EMEA, 2, 2021, 23000 1111111, EMEA, 4, 2021, 19000 1111111, EMEA, 5, 2021, 2000 1111111, EMEA, 7, 2021, 14000 ]; Fulltrans: NoConcatenate LOAD IF(isnull(Material), Peek('Material'), Material) as Material, IF(isnull(Region), Peek('Region'), Region) as Region, IF(isnull(Stock), Peek('Stock'), Stock) as Stock, Month, Year Resident TMP_Fulltrans Order By YearMonth ; Drop field YearMonth; DROP table TMP_Fulltrans;

Re: Populate Missing Data for all Materials

gauravs275 — Fri, 12 Nov 2021 05:32:09 GMT

Hello @Vegar ,

Thank you for your answer.

Unfortunately the data I am working with starts from 2015 and as per your suggested method, hardcoding the number of months is not an option I can use.

Please instruct me on how I can modify your solution to suit my requirements.

Thanks,

Gaurav

Re: Populate Missing Data for all Materials

Vegar — Fri, 12 Nov 2021 15:39:50 GMT

Instead of hard coding the year and months you should find a way to generate a table that contains all you desires year and month combinations. Basically all month and year combinations between your min period and max period. You also need to figure out a way to ensure that you read your joined table in a chronological order (thats why I created YearMonth in my example).