topic Re: Generate Null Values for Every Non Existing Record/Date in Connectivity & Data Prep

Generate Null Values for Every Non Existing Record/Date

pedrohenriqueperna — Tue, 06 May 2025 21:00:34 GMT

Hi,

I'm having a hard time figuring out a way to generate null values for every dimension value that has no record for.

For instance, lets use this as an example:

fruits:
LOAD * Inline [fruit,date,value
orange,1,100
orange,2,200
orange,3,300
apple,2,200
apple,3,300
apple,5,700
banana,1,100
banana,4,400
banana,5,500
];

date:
LOAD * Inline [date
1
2
3
4
5
];

final:
LOAD * Inline [fruit,date,value
orange,1,100
orange,2,200
orange,3,300
orange,4,
orange,5,
apple,1,
apple,2,200
apple,3,300
apple,4,
apple,5,700
banana,1,100
banana,2,
banana,3,
banana,4,400
banana,5,500
];

Table fruits has the transactional data. The date table would be the master calendar that contains all dates (lets use an integer to make it simpler). The table final is the expected result, where all fruits now have null value record for every missing "date".

Any suggestions?

Thanks in advance.

Re: Generate Null Values for Every Non Existing Record/Date

pedrohenriqueperna — Tue, 06 May 2025 21:25:33 GMT

Ok, after reading this topic I've managed to do it:

https://community.qlik.com/t5/Member-Articles/Generating-Missing-Data-In-QlikView/ta-p/1491394?attachment-id=151545

Thanks @hic

date:
LOAD * Inline [date
1
2
3
4
5
6
7
8
9
10
];

LET vMinDate = 1;
LET vMaxDate = 10;

temp_fruit:
LOAD Distinct fruit Resident fruit;

Join (temp_fruit)
LOAD RecNo()+$(vMinDate)-1 as date
AutoGenerate vMaxDate - vMinDate + 1;

Concatenate(fruit)
LOAD * Where Not Exists(tempkey);
LOAD fruit,
date,
fruit&'|'&date as tempkey
Resident temp_fruit;

Drop Tables temp_fruit;

Drop Field tempkey;

Re: Generate Null Values for Every Non Existing Record/Date

hic — Wed, 07 May 2025 12:19:53 GMT

You're welcome!