topic Re: Before and after date calculation in QlikView

Before and after date calculation

Mon, 06 May 2013 19:18:11 GMT

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.

Re: Before and after date calculation

Mon, 06 May 2013 20:06:07 GMT

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

Re: Before and after date calculation

Tue, 07 May 2013 15:36:05 GMT

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.

Re: Before and after date calculation

jagan — Tue, 07 May 2013 16:00:09 GMT

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.

Re: Before and after date calculation

Tue, 07 May 2013 16:40:33 GMT

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;

Re: Before and after date calculation

jagan — Tue, 07 May 2013 16:46:58 GMT

Superb.

Regards,

Jagan.