Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mdmukramali
Valued Contributor II

Previous record value as new field

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Previous record value as new field

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;

6 Replies
ramoncova06
Valued Contributor III

Re: Previous record value as new field

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;

MVP
MVP

Re: Previous record value as new field

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
Valued Contributor II

Re: Previous record value as new field

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
Valued Contributor III

Re: Previous record value as new field

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
Valued Contributor

Re: Previous record value as new field

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
Valued Contributor II

Re: Previous record value as new field

Thanks,

Community Browser