topic Add missing records in QlikView

Add missing records

Mon, 12 Mar 2012 07:53:09 GMT

Hi all,

i have aproble, which i thought was solved, but the solution created over 200000000 records in a table and my qv app was running out of memory.

Here is what i have and need to accomplish: I have one table:

YearMonth	ID	Amount
201201	1	14
201202	1	12
201201	2	11
201202	2	13
201203	2	9
201201	3	12

The table above lists for each month and ID in the current year an amount - IF there is one. If there is none, there is just no record.

What i need: I want for each ID where there is no amount and hence there is no record for this month a dummy record with amount = 0. For the table above this means:

YearMonth	ID	Amount
201201	1	14
201202	1	12
201203	1	0
201201	2	11
201202	2	13
201203	2	9
201201	3	12
201202	3	0
201203	3	0

I want to do all this in the LOAD script.

Thanks a lot!

Add missing records

SunilChauhan — Mon, 12 Mar 2012 08:12:02 GMT

properties -> presentation-> uncheck Supress zero values

hope this helps

Add missing records

Mon, 12 Mar 2012 08:22:34 GMT

I need to do this in the LOAD script...

Thanks.

Add missing records

SunilChauhan — Mon, 12 Mar 2012 08:26:46 GMT

in script

try one of below

if(amount=' ',0,Amount) as amount

if(len(amount)=0,0,Amount) as amount

if(isnull(amount)=-1,0,Amount) as amount

hope this helps

Add missing records

Mon, 12 Mar 2012 08:42:37 GMT

Sorry, this does not do anything as there is just no record to check on.

I think the check must be done on a missing YearMonth for a given ID, but i am just not sure how to do this.

Add missing records

CELAMBARASAN — Mon, 12 Mar 2012 08:49:00 GMT

Hi,

Could you tell me why you need to add the missing records?

Celambarasan