Generate Null Values for Every Non Existing Record/Date

[pedrohenriqueperna]

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.

[pedrohenriqueperna · Accepted solution]

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?attac...

Thanks @hic 

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

LET vMinDate = 1;
LET vMaxDate = 10;

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

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;

[hic]

You're welcome!

See also Qlik according to HIC 

HIC