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.

[#2]

I qould to obtain something like this:

load
date(makedate(2011, 11,1)+recno()-1) as posting
autogenerate 15 ;

Load * INLINE [
product , posting, Qt
AAA  ,   04/11/2010  ,5
AAA   ,  10/11/2010  ,  10
]:

results I would to obtain with new fields(newItem ,newPosting ,new Qt)

newItem  newPosting  new Qt
AAA     01/11/2010   0
AAA     02/11/2010   0
AAA     03/11/2010   0
AAA     04/11/2010   5
AAA     05/11/2010   0
AAA     06/11/2010   0
AAA     07/11/2010   0
AAA     08/11/2010   0
AAA     09/11/2010   0
AAA     10/11/2010   10
AAA     11/11/2010   0
AAA     12/11/2010   0
AAA     13/11/2010   0
........

Thanks
Regards
K.

[Miguel_Angel_Baeyens]

Hi,

Use the Previous() function to get the previous record values

If(Len(Previous(Qt)) > 0,  Qt, 0) AS Qt

Hope that helps,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

I Attached a file.. the result doesn't match

[swuehl]

Hi k_l_a,

maybe like:

AA:

Load * INLINE [

product , posting , Qt

AAA  ,   04/11/2010 , 5

AAA  ,  10/11/2010 ,  10

];

right join (AA) load

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

autogenerate 15 ;

bb:

load

posting as newposting, Qt as newQT,

if(isnull(product),peek(newProduct),product) as newProduct

resident AA order by product desc;

Drop table AA ;

I merged the calendar to your AA table (maybe that is not the best idea, but just to demonstrate), then we have all the dates to go through and use peek() to fill in the missing product names (if we have a fixed name, something like

'AAA' as newProduct

will do also.

I am not sure how you would like to handle the product names when there are multiple products, but above reproduces your sample.

Regards,

Stefan

[#6]

In fact it doesn't work if if I add an other product:

oad * INLINE [

product , posting , Qt

AAA  ,   04/11/2010 , 5

AAA  ,  10/11/2010 ,  10

BB , 02/11/2010,11

BB , 08/11/2010,15

]; 

[swuehl]

Yes,

but how do you want to handle this?

In your original post, the product name was set with a name 'AAA' even before the first posting for that product.

So how should the table look like with multiple products (and possible intersecting periods) ?

[#8]

The target is to handle all products and to create a new posting date
thath contains all posting generate by merging Calendar Masterand old posting field

The result I need from the Inline I posted is:

AAA 01/11/2010   0
AAA 02/11/2010   0
AAA 03/11/2010   0
AAA 04/11/2010   5
AAA 05/11/2010   0
AAA 06/11/2010   0
AAA 07/11/2010   0
AAA 08/11/2010   0
AAA 09/11/2010   0
AAA 10/11/2010    10
........

BB 01/11/2010   0
BB 02/11/2010   11
BB 03/11/2010   0
BB 04/11/2010   5
BB 05/11/2010   0
BB 06/11/2010   0
BB 07/11/2010   0
BB 08/11/2010   15
BB 09/11/2010   0
BB 10/11/2010   0

.......

[swuehl]

If you just need to display the data like you posted above, I think the solution could just be a separate calendar to draw your data against, like in attached sample.

Or do you really need to generate the tables in your data model?

Regards,

Stefan

[#10]

Is correct "virtually" becouse the Qt are create in  the object not in the field. The zero value  does not in the new field but is  created in the object by if statement..

I need the field becouse I wil use the new field  wty and Date to other operations.

Thanks