topic Previous record value as new field in QlikView

Previous record value as new field

mdmukramali — Tue, 10 May 2016 14:19:12 GMT

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

Re: Previous record value as new field

ramoncova06 — Tue, 10 May 2016 14:28:58 GMT

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;

Re: Previous record value as new field

MK_QSL — Tue, 10 May 2016 14:36:23 GMT

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;

Re: Previous record value as new field

mdmukramali — Tue, 10 May 2016 14:37:10 GMT

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,

Re: Previous record value as new field

ramoncova06 — Tue, 10 May 2016 14:41:46 GMT

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;

Re: Previous record value as new field

santiago_respane — Tue, 10 May 2016 14:42:49 GMT

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,

Re: Previous record value as new field

mdmukramali — Thu, 12 May 2016 07:41:05 GMT

Thanks,