load should fill in the missing values not as null but as 0

[amien]

double post

[#2]

Another try LOL

Calculation:
LOAD * INLINE [
date, filenumber, value
200901, 1, 500
200902, 2, 500
];

OUTER JOIN (Calculation)
LOAD
 date,
 '0' AS filenumber,
 '0' AS value
RESIDENT basic;

Now make a pivot table with the expression '=Sum(filenumber)' instead of the dimension filenumber.

And of course the second expression '=Sum(value)'

Perhaps not what you want with the filenumber field though ...

[amien]

nope .. doesnt give me what i want

[#4]

Then I probably don't quite understand what you like to see/accomplish. I hope you can work from this though 🙂

[amien]

i want this be possible a straight tablebox:

from these few records:

200901, 1, 500

200901, 2, 100

200902, 2, 200

to this: (without using NULL() but really 0 (zeroes)

200901, 1, 500

200902, 1, 0

200903, 1, 0

......

200912, 2, 0

200901, 2, 100

200902, 2, 200

200903, 2, 0

......

200912, 2,0

then a pivot with two dimensions: filenumber and date

and a sum of payment.

i can do this also with checking show NULL values in the pivot.. but this makes my timeline a bit harder.

[#6]

Ah ...

for j=200901 to 200912

basic:
LOAD * INLINE [
 date
 $(j)
 ];

next j;

Calculation:
LOAD * INLINE [
date, filenumber, value
200901, 1, 500
200902, 2, 500
200903, 3, 250
];


For i=1 to 3 // enter the maximum filenumber here

OUTER JOIN (Calculation)
LOAD
 date,
 $(i) AS filenumber,
 '0' AS value
RESIDENT basic;

next i;

[amien]

thanks again for your reply ..

yes .. and no 🙂

yes .. the output is what i need .. the only problem is that the data of the filenumbers are just an example.

its possible that the filenumbers are not follow-up numbers. for example :

LOAD * INLINE [
date, filenumber, value
200901, 334212, 500
200902, 212123, 500
200903, 800345, 250
];

[#8]

LOL but then this should do it 😉

for j=200901 to 200912

basic:
LOAD * INLINE [
 date
 $(j)
 ];

next j;

Calculation:
LOAD * INLINE [
date, filenumber, value
200901, 1324234, 500
200902, 2543444, 500
200902, 8566554, 250
];

For i=0 to NoOfRows('Calculation')-1; // being the number of records in Calculation-1

LET F=Peek('filenumber',$(i),'Calculation');

//OUTER JOIN (Calculation)
CONCATENATE
LOAD
 date,
 $(F) AS filenumber,
 '0' AS value
RESIDENT basic;

next i;

[amien]

great stuff Mark .. thanks!

[#10]

No problem.

Glad I could help 🙂

[amien]

issue:

when a filenumber has a value. It will generate two records:

filenumber, date,value

32333, 200901, 500

32333, 200901, 0

if a filenumber with date doesnt exist .. then only a value with 0 will be created

i only need one ..