Previous record value as new field

[mdmukramali]

Dear,

let me explain the scenario,

I have 3 fields in my sample table.

DOCUMENT_NO,

  DEPT_ID,

  SENT_DATE

each document process through department and when ever any department send the document to other department SENT_DATE will be generated and stored.

That SENT_DATE is Receiving date for other department.

so i'm trying to create new field in the script which contain receiving date of the document department level.

then difference between Document Receiving date  and SENT_DATE .

kindly find the attached sample file and Qvd.

Thanks,

Mukram

[ramoncova06]

if you are only looking for the previous value

you can use peek without the -2

DOCS:

LOAD   DOCUMENT_NO,

  TREATMENT_ID as DEPT_ID,

     SENT_DATE

FROM

DOCS.QVD

(qvd);

left join (DOCS)

load

  *,

  if(Previous(DOCUMENT_NO)=DOCUMENT_NO, Peek(SENT_DATE)) as RECEIVED_DATE

Resident DOCS

order by DOCUMENT_NO,SENT_DATE desc;

[MK_QSL · Accepted solution]

DOCS:

LOAD

  DOCUMENT_NO,

  TREATMENT_ID as DEPT_ID,

    SENT_DATE

FROM

DOCS.QVD

(qvd)

;

Left join (DOCS)

load

  *,

  if(Previous(DOCUMENT_NO)=DOCUMENT_NO, Previous(SENT_DATE)) as RECEIVED_DATE

Resident DOCS

order by DOCUMENT_NO,SENT_DATE;

[mdmukramali]

Dear ,

Thanks for your reply.

I try peek function but i'm getting the next value in the new field.

i need previous value in the new field.

find the attached screen shot.

Thanks,

[ramoncova06]

My bad, I just noticed under order you want the set date to be asc instead of desc

DOCS:

LOAD   DOCUMENT_NO,

  TREATMENT_ID as DEPT_ID,

     SENT_DATE

FROM

DOCS.QVD

(qvd);

left join (DOCS)

load

  *,

  if(Previous(DOCUMENT_NO)=DOCUMENT_NO, Peek(SENT_DATE)) as RECEIVED_DATE

Resident DOCS

order by DOCUMENT_NO,SENT_DATE asc;

[santiago_respane]

This solves your issue:

DOCS:

LOAD DOCUMENT_NO,

  TREATMENT_ID as DEPT_ID,

     SENT_DATE

FROM DOCS.QVD (qvd);

DOCS_2:

NOCONCATENATE

load

  *,

  if(Previous(DOCUMENT_NO)=DOCUMENT_NO, Previous(SENT_DATE)) as RECEIVED_DATE

Resident DOCS

order by DOCUMENT_NO,SENT_DATE ASC;

DROP TABLE DOCS;

Hope this helps.

Kind regards,

[mdmukramali]

Thanks,