Add missing records

[#1]

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!

[SunilChauhan]

properties -> presentation-> uncheck Supress zero values

hope this helps

[#3]

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

Thanks.

[SunilChauhan]

in script

try one of below

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

or

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

or

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

hope this helps

[#5]

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.

[CELAMBARASAN]

Hi,

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

Celambarasan