Populate data for missing weeks

prasadmundewadi — Sat, 16 Nov 2024 01:16:17 GMT

Hi Qlik experts

I receive a file weekly that has company sales stage data. I only receive data for a company in a week if there is a change in the stage. I have a requirement to populate missing data in the weeks where I did not receive the data so that we can show week over week progress of how the sales stage move. Here is what I have and what I would like the output to be.

Input data:

FileReceivedDate	Id	Company	Stage
01/01/20	1	Apple	Open
01/01/20	2	IBM	Open
01/01/20	3	Microsoft	Open
01/08/20	1	Apple	In Progress
01/08/20	4	Nike	Open
01/15/20	2	IBM	In Progress
01/15/20	4	Nike	In Progress

-------------------

Here is Expected Output

FileReceivedDate	Id	Company	Stage
01/01/20	1	Apple	Open
01/01/20	2	IBM	Open
01/01/20	3	Microsoft	Open
01/08/20	1	Apple	In Progress
01/08/20	4	Nike	Open
*01/08/20*	2	*IBM*	*Open*	*new row to be created. Although we did not receive data for IBM we want it to be counted in*
01/08/20	3	Microsoft	Open	new row to be created. Although we did not receive data for MS we want it to be counted in
01/15/20	2	IBM	In Progress
01/15/20	4	Nike	In Progress
01/15/20	1	Apple	In Progress	new row to be created. Although we did not receive data for Apple we want it to be counted in
01/15/20	3	Microsoft	Open	new row to be created. Although we did not receive data for MS we want it to be counted in

I have tried few things by doing cartesian product between this table and a WeekStartDates table. But not able to get the exact result I am looking for. Any help? Thanks in advance for your help.

Re: Populate data for missing weeks

PriyankaShivhare — Tue, 20 Oct 2020 02:04:48 GMT

populate a Master Calendar using min and max date of filereceiveddate and link it with your table containing all the required fields and use the calendar date... you should be able to receive the missing dates coming from your master calendar

Re: Populate data for missing weeks

prasadmundewadi — Tue, 20 Oct 2020 02:46:30 GMT

I am not sure I understand. E.g. In result table I need 3 rows for Apple. How will this generate the 3rd row? If I have a master calendar table how will out join with the main table?

topic Re: Populate data for missing weeks in App Development

Populate data for missing weeks

Re: Populate data for missing weeks

Re: Populate data for missing weeks