Before and after date calculation

[#1]

Hi All,

I need to populate a custom table as shown in the picture.

I have table with 3 fields .

Account ID , Order Number and order date.

I would like to add two aditional columns, say PREVIOUS and NEXT

PREVIOUS will store the order date from the JUST previous order that accouht placed and NEXT column  will store the order date from the IMMIDIATE next order the account placed.

If there is no history for previous or next order , it will put NONE in those fields (as shown in the table)

Would someone please help me in doing this during the load?

(and may be in an expression in a table chart?)

Thanks,

Aji Paul.

[#2]

Have you tried using above() and below()?

For instance: Previous date would be -> above(ORDER_DT) and Next date would be below(ORDER_DT).

Hope that helps,

Matt

[#3]

Matt,

Thanks for the reply!

I tried using it in a calc DIM expression and it gave me the following error  "// Error in calculated dimension"

Also how do we use this in load script?

[ORDER_TEMP]:
LOAD * inline  [
ACC,ORD_NUM,ORD_DT
A001,SL001,'1/1/2010'
A001,SL002,'1/1/2011'
A001,SL003,'1/1/2012'
A003,SL004,'5/1/2012'
A003,SL005,'8/1/2012'
A003,SL006,'3/1/2013'
A004,SL007,'2/28/2010'
A005,SL008,'5/31/2012'
A008,SL009,'3/1/2010'
A008,SL010,'5/2/2010'
A008,SL011,'6/3/2010'
A008,SL012,'9/9/2010'
A008,SL013,'1/1/2011'
A008,SL014,'2/1/2012'
A008,SL015,'4/1/2013'
A009,SL016,'6/1/2012'
A009,SL017,'5/1/2013'
]

;

what I would like to do is

[FINAL_TAB]:

load ACC,ORD_NUM,ORD_DT , somethingtogetPREVIOUS(ORD_DT) as previous_ORDER , somethingtogetNEXT(ORD_DT) as next_ORDER resident ORDER_TEMP;

drop table ORDER_TEMP;

Thanks,

Aji Paul.

[jagan · Accepted solution]

Hi,

Please try below script

OrderTemp:

LOAD

    ACC,

    ORD_NUM,

    Date(ORD_DT) AS OrderDate,

    If(ACC = Previous(ACC), Previous(Date(ORD_DT)), 'NONE') AS Previous;

LOAD * inline  [

ACC,ORD_NUM,ORD_DT

A001,SL001,1/1/2010

A001,SL002,1/1/2011

A001,SL003,1/1/2012

A003,SL004,5/1/2012

A003,SL005,8/1/2012

A003,SL006,3/1/2013

A004,SL007,2/28/2010

A005,SL008,5/31/2012

A008,SL009,3/1/2010

A008,SL010,5/2/2010

A008,SL011,6/3/2010

A008,SL012,9/9/2010

A008,SL013,1/1/2011

A008,SL014,2/1/2012

A008,SL015,4/1/2013

A009,SL016,6/1/2012

A009,SL017,5/1/2013

];

Order:

LOAD

    *,

    If(ACC = Previous(ACC), Previous(OrderDate), 'NONE') AS Next

RESIDENT OrderTemp

ORDER BY ACC, OrderDate DESC;

DROP TABLE OrderTemp;

Hope it helps you.

Regards,

Jagan.

[#5]

Jagan,

That is awesome!

The only change I did was moved the previous and next calc after the initial load.

Thanks again!

Aji paul.

OrderTemp:

LOAD * inline  [
ACC,ORD_NUM,ORD_DT
A001,SL001,1/1/2010
A001,SL002,1/1/2011
A001,SL003,1/1/2012
A003,SL004,5/1/2012
A003,SL005,8/1/2012
A003,SL006,3/1/2013
A004,SL007,2/28/2010
A005,SL008,5/31/2012
A008,SL009,3/1/2010
A008,SL010,5/2/2010
A008,SL011,6/3/2010
A008,SL012,9/9/2010
A008,SL013,1/1/2011
A008,SL015,4/1/2013
A008,SL014,2/1/2012
A009,SL016,6/1/2012
A009,SL017,5/1/2013
];



Order:
LOAD  *, If(ACC = Previous(ACC), Previous(ORD_DT), 'NONE') AS Next
RESIDENT OrderTemp ORDER BY ACC, ORD_DT DESC;
join(Order) LOAD *, If(ACC = Previous(ACC), Previous(ORD_DT), 'NONE') AS  Previous
RESIDENT OrderTemp ORDER BY ACC, ORD_DT asc;

DROP TABLE OrderTemp;

[jagan]

Superb.

Regards,

Jagan.