Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
mdmukramali
Specialist III
Specialist III

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

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

1.png

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;

View solution in original post

8 Replies
sorrakis01
Specialist
Specialist

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
MVP
MVP

1.png

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
Specialist
Specialist

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
Specialist III
Specialist III

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

Anonymous
Not applicable

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
Not applicable

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
Partner - Specialist
Partner - Specialist

Hi,

Check the qvw.

Hope it helps you!!!

Regards,

Kavita

mohammadkhatimi
Partner - Specialist
Partner - Specialist

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