To get the Next Document TRANSFER_DATE (by using Previous or Peek Function at script level)

[mdmukramali]

Dear All,

i have 3 fields

DOC_NO,     //document number

SEQ,

TRANSFER_DATE.

i want to create a NEXT_TRANSFER_DATE of the document as a new field in the script level.

NEXT_TRANSFER_DATE value based on the DOC_NO and SEQ order.

sample data

load * inline

DOC_NO,SEQ,TRANSFER_DATE

[

101, 1, 05/05/2015

101, 2, 06/05/2015

101, 3 ,09/05/2015

101, 4 ,18/05/2015

102, 1, 05/05/2015

102, 2, 18/05/2015

103, 1, 05/05/2015

104, 1, 06/05/2015

104, 2 ,14/05/2015

104, 3 ,25/06/2015

]

;

i think we need use Previous or Peek function at script to get the below desired result.

OUTPUT EXPECTED:

DOC_NO,SEQ,TRANSFER_DATE , NEXT_TRANSFER_DATE

101, 1, 05/05/2015 , 06/05/2015

101, 2, 06/05/2015 , 09/05/2015

101, 3 ,09/05/2015 , 18/05/2015

101, 4 ,18/05/2015 ,  TODAY's DATE    // for this document it's the last SEQ so i need for last SEQ today's date

102, 1, 05/05/2015,  18/05/2015

102, 2, 18/05/2015 , TODAY's DATE

103, 1, 05/05/2015 , TODAY's DATE

104, 1, 06/05/2015 , 14/05/2015

104, 2 ,14/05/2015 , 25/06/2015

104, 3 ,25/06/2015 , TODAY's DATE

if anyone need more explanation please let me know.

Thanks,

Mukram

[sorrakis01]

Hi,

Try this:

LOAD

DOC_NO,SEQ,TRANSFER_DATE,

If(IsNull(Peek(TRANSFER_DATE)),TRANSFER_DATE,Peek(TRANSFER_DATE)) as New_Date;;

Dates:

load * inline

[DOC_NO,SEQ,TRANSFER_DATE

101, 1, 05/05/2015

101, 2, 06/05/2015

101, 3 ,09/05/2015

101, 4 ,18/05/2015

102, 1, 05/05/2015

102, 2, 18/05/2015

103, 1, 05/05/2015

104, 1, 06/05/2015

104, 2 ,14/05/2015

104, 3 ,25/06/2015

];

Regards

[maxgro · Accepted solution]

source:

load * inline

[

DOC_NO,SEQ,TRANSFER_DATE

101, 1, 05/05/2015

101, 2, 06/05/2015

101, 3 ,09/05/2015

101, 4 ,18/05/2015

102, 1, 05/05/2015

102, 2, 18/05/2015

103, 1, 05/05/2015

104, 1, 06/05/2015

104, 2 ,14/05/2015

104, 3 ,25/06/2015

];

left join (source)

load

  *,

  if(Peek(DOC_NO)=DOC_NO, Peek(TRANSFER_DATE), Today()) as NEXT_TRANSFER_DATE

Resident source

order by DOC_NO, SEQ desc;

[qlikmsg4u]

Load *, If(Previous(DOC_NO) = DOC_NO,Peek(TRANSFER_DATE),Today()) as NEW_TRANSFER_DATE;

load * inline [

DOC_NO,SEQ,TRANSFER_DATE

101, 1, 05/05/2015

101, 2, 06/05/2015

101, 3 ,09/05/2015

101, 4 ,18/05/2015

102, 1, 05/05/2015

102, 2, 18/05/2015

103, 1, 05/05/2015

104, 1, 06/05/2015

104, 2 ,14/05/2015

104, 3 ,25/06/2015

];

[senpradip007]

Have a look on the attachment. Hope it will help you.

[Anonymous]

Try This

sampledata:
Load DOC_NO,SEQ,Date#(TRANSFER_DATE,'YYYY-MM-DD') as TRANSFER_DATE Inline [
DOC_NO,SEQ,TRANSFER_DATE
101, 1, 05/05/2015
101, 2, 06/05/2015
101, 3 ,09/05/2015
101, 4 ,18/05/2015
102, 1, 05/05/2015
102, 2, 18/05/2015
103, 1, 05/05/2015
104, 1, 06/05/2015
104, 2 ,14/05/2015
104, 3 ,25/06/2015

];

sampledataNew:
Load
DOC_NO,
SEQ,
TRANSFER_DATE,
Date(If(Previous(DOC_NO)=DOC_NO, Date#(PREVIOUS(TRANSFER_DATE),'DD/MM/YYYY'),  Today()),'YYYY-MM-DD') As EndDate
Resident
sampledata
Order By DOC_NO, TRANSFER_DATE Desc;

Drop Table sampledata;

[Anonymous]

Try this....

sampledata:
Load DOC_NO,SEQ,Date#(TRANSFER_DATE,'YYYY-MM-DD') as TRANSFER_DATE Inline [
DOC_NO,SEQ,TRANSFER_DATE
101, 1, 05/05/2015
101, 2, 06/05/2015
101, 3 ,09/05/2015
101, 4 ,18/05/2015
102, 1, 05/05/2015
102, 2, 18/05/2015
103, 1, 05/05/2015
104, 1, 06/05/2015
104, 2 ,14/05/2015
104, 3 ,25/06/2015

];

sampledataNew:
Load
DOC_NO,
SEQ,
TRANSFER_DATE,
Date(If(Previous(DOC_NO)=DOC_NO, Date#(PREVIOUS(TRANSFER_DATE),'DD/MM/YYYY'),  Today()),'DD/MM/YYYY') As EndDate
Resident
sampledata
Order By DOC_NO, TRANSFER_DATE Desc;

Drop Table sampledata;

[kavita25]

Hi,

Check the qvw.

Hope it helps you!!!

Regards,

Kavita

[mohammadkhatimi]

HIe....

Inline:

load * inline

[ DOC_NO,SEQ,TRANSFER_DATE

101, 1, 05/05/2015

101, 2, 06/05/2015

101, 3 ,09/05/2015

101, 4 ,18/05/2015

102, 1, 05/05/2015

102, 2, 18/05/2015

103, 1, 05/05/2015

104, 1, 06/05/2015

104, 2 ,14/05/2015

104, 3 ,25/06/2015

]

;

load*,

  if(Peek(DOC_NO)=DOC_NO, Peek(TRANSFER_DATE), date(Today(),'DD/MM/YYYY')) as NEXT_TRANSFER_DATE

  Resident Inline Order by DOC_NO,SEQ desc;

drop table Inline

exit Script

Hope above script helps you...

Regards,

Mohammad