posting

[#1]

hs case:

product  posting       qty

AAA  10/11/2010        10
BBB  05/11/2010         5
AAA      05/02/2010      2
BBB  10/11/2010  5

I need to create a  FIELDS  contains the zero value if
the previous posting  was null and the posting must be
start from 01/11/20010.
Thanks
regards,
K.

[swuehl]

Ok,

I understood (so just for curiosity: what are you calculating that you need a value of 0 in your data model tables for each other day?)

I created a loop in the script over all product names, then create the calendar load within the loop and a lookup for the qty values.

This is my last try,

Stefan

[#3]

Interesting  ... probabil is the solution but I need to  check in the loop over 10000  product ...

[swuehl · Accepted solution]

Well, I haven't said that this is performing well.

Maybe this is faster (not using Lookup):

INPUT:

LOAD *, product & posting as ProductPosting;

Load * INLINE [

product , posting , Qt

AAA  ,   04/11/2010 , 5

AAA  ,  10/11/2010 ,  10

BB , 02/11/2010,11

BB , 08/11/2010,15

]; 

ProductNamesTable:

LOAD Distinct product as ProductNames

RESIDENT INPUT;

FOR i = 1 TO NoOfRows('ProductNamesTable')

SET vTempFieldName = FieldValue('ProductNames', $(i));

RESULT:

LOAD

$(vTempFieldName) as newProduct,

date(makedate(2010, 11,1)+recno()-1) as newPosting

autogenerate 15 ;

NEXT

left join (RESULT) LOAD posting as newPosting, product as newProduct, Qt as newQT

resident INPUT;

[#5]

Ok. The script you suggest generate over 5.000.000 of rows but is correct.

No memory crash.

Many Thanks

Regards,

Kla

[swuehl]

Good to hear!

(Regarding the 5 M rows I assume that's because of your Product Count  X CalendarDay Count, right? Or are you saying that you expected a much smaller row number?)

[#7]

Dear friend of QV,

I need to add some dimensions:

lot no and location.

the result for Item is correct but now I need to add the result for Location and lot...

Load * INLINE [

product , posting , Qt , location, lot

AAA  ,   04/11/2010 , 5 ,  ita ,  123
AAA  ,  10/11/2010 ,  10 , fra , 107
AAA  ,   14/11/2010 , 25 ,  ita ,  123
BB , 02/11/2010,11 , ita, 875
BB , 08/11/2010,15 , ger, 41

]; 

[swuehl]

Well, if I understood you correctly, just replace the last left join with

left join (RESULT) LOAD posting as newPosting, product as newProduct, Qt as newQT, location as newLocation, lot as newLot

resident INPUT;

See also attached.

Stefan

[#9]

Please to build the progressive sum I need to use this script:

inv:
load * INLINE [

post, item , QTY , location , lot

01/01/2011 , AAA, 100, ita , 123
05/01/2011 , CCC, 26 , ita, 852
01/01/2011 , CCC, -4  , ita ,900
25602/01/2011 , AAA ,-16 , fra, 40
03/01/2011, AAA, -4 , ita. 1028
05/01/2011, FFF,70 , eng. 700
] ;

pk:
load
post as pos
, item  as itm,
QTY as qq
resident inv

ORDER BY  item , post ;

drop table inv ;

load
pos,  itm ,
numsum(peek('QTYTOTAL') , qq) as QTYTOTAL
resident pk  ;

drop table  pk

But I'm not able to add the lot and Location dimensions to create the correct progressive sum and correct group of data field  Location and lot

After I have to build the zero value as you suggest...

Thanks

thanks

[swuehl]

Could you give an example of how the result should look like with the above table as input? Do you still want or need all be done in the script?

[#11]

Please start from the file I attached. The data was built in progress way..

In this case I have a dimension Location.. How I have to menage the dimension?