Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prasadmundewadi
Contributor III
Contributor III

Populate data for missing weeks

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:

FileReceivedDateIdCompanyStage
01/01/201AppleOpen
01/01/202IBMOpen
01/01/203MicrosoftOpen
01/08/201AppleIn Progress
01/08/204NikeOpen
01/15/202IBMIn Progress
01/15/204NikeIn Progress

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

Here is Expected Output

FileReceivedDateIdCompanyStage 
01/01/201AppleOpen 
01/01/202IBMOpen 
01/01/203MicrosoftOpen 
01/08/201AppleIn Progress 
01/08/204NikeOpen 
01/08/202IBMOpennew row to be created. Although we did not receive data for IBM we want it to be counted in 
01/08/203MicrosoftOpennew row to be created. Although we did not receive data for MS we want it to be counted in 
01/15/202IBMIn Progress 
01/15/204NikeIn Progress 
01/15/201AppleIn Progressnew row to be created. Although we did not receive data for Apple we want it to be counted in 
01/15/203MicrosoftOpennew 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.

Labels (1)
2 Replies
PriyankaShivhare
Creator II
Creator II

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
prasadmundewadi
Contributor III
Contributor III
Author

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?