Populate Missing Data for all Materials

[gauravs275]

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.

[Or]

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/14706...

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

[Vegar]

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;

[gauravs275]

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

[Vegar]

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).