Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
danaleota1
Creator
Creator

Creating dummy records for missing data in a table

I have the following table:

[SLATable]:

Load
Date,
INSTITUTIONAME,
INTSANS,
SL1,
SLA

FROM [lib://myqvd.qvd]
(qvd)

There are some INSTITUTIONNAME & Date combinations that won't always populate records for a given day Ion these dates I would still like records and have the SL1 field and INTSANS field to populate zeros so I have dummy records in order to populate a pivot table with accumulative sums.  How would I create dummy records in this case?  Thank you.  

1 Solution

Accepted Solutions
kaushiknsolanki
Luminary Alumni
Luminary Alumni

Hi,

You should use the Cartesian product to generate the missing values. Like below.

Temp:
Load Distinct INSTITUTIONAME
FROM [lib://myqvd.qvd]
(qvd);

Join (Temp)

Load Distinct Date
FROM [lib://myqvd.qvd]
(qvd);

MainTable:
Load INSTITUTIONAME & '-'& Date as Key,
INSTITUTIONAME ,
Date
Resident Temp;

Drop table Temp;

Left join(MainTable)
Load *,INSTITUTIONAME & '-'& Date as Key
FROM [lib://myqvd.qvd]
(qvd);

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

3 Replies
kaushiknsolanki
Luminary Alumni
Luminary Alumni

Hi,

You should use the Cartesian product to generate the missing values. Like below.

Temp:
Load Distinct INSTITUTIONAME
FROM [lib://myqvd.qvd]
(qvd);

Join (Temp)

Load Distinct Date
FROM [lib://myqvd.qvd]
(qvd);

MainTable:
Load INSTITUTIONAME & '-'& Date as Key,
INSTITUTIONAME ,
Date
Resident Temp;

Drop table Temp;

Left join(MainTable)
Load *,INSTITUTIONAME & '-'& Date as Key
FROM [lib://myqvd.qvd]
(qvd);

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

danaleota1
Creator
Creator
Author

Hi Kaushik that worked.  My pivot table now has an accumulative count where there had been no records before.  

 

Thank you!