Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This is a fairly common question - have a look at Henric's great explanation for it here:
https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394
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;
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
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).