topic Re: Script for below logic? in QlikView

Script for below logic?

dmohanty — Sat, 07 Mar 2015 20:20:09 GMT

Hi All,

I have a data set like this above. From this I need to pick only those DOC_NUM and DOC_NUM_ID,

where POST_DATE = min(POST_DATE) and POST_DATE = min(POST_DATE) +1
In above scenario, 2 records would be picked up like this below.

What could be the LOAD Script for this?

Re: Script for below logic?

rwunderlich — Sat, 07 Mar 2015 22:07:30 GMT

Here's one approach:

Inner JOIN (data)

LOAD

POST_DATE + Iterno() as POST_DATE

While IterNo() < 3

;

LOAD min(POST_DATE)-1 as POST_DATE

Resident data

;

-Rob

Re: Script for below logic?

sunny_talwar — Sun, 08 Mar 2015 02:08:23 GMT

You can use this script if you want to select minimum date and second lowest date (not just the min date + 1)

Table:

LOAD * INLINE [

DOC_NUM, DOC_NUM_ID, RCV_QTY, POST_DATE

10001, 10, 6, 01/14/2015

10001, 20, 5, 01/20/2015

10001, 30, 4, 01/15/2015

10001, 30, 4, 01/27/2015

10001, 40, 4, 01/28/2015

10002, 10, 6, 01/14/2015

10002, 20, 5, 01/20/2015

10002, 30, 4, 01/16/2015

10002, 30, 4, 01/27/2015

10002, 40, 4, 01/28/2015

];

Table1:

LOAD DOC_NUM,

Date(Min(POST_DATE)) as POST_DATE1

Resident Table

Group By DOC_NUM;

Concatenate (Table1)

LOAD DOC_NUM,

Date(Min(POST_DATE, 2)) as POST_DATE1

Resident Table

Group By DOC_NUM;

Left Join (Table1)

LOAD *

Resident Table;

DROP Table Table;

HTH

Best,

Re: Script for below logic?

anbu1984 — Sun, 08 Mar 2015 07:50:55 GMT

Temp:

LOAD DOC_NUM, DOC_NUM_ID, RCV_QTY, Date#(POST_DATE,'MM/DD/YYYY') As POST_DATE INLINE [

DOC_NUM, DOC_NUM_ID, RCV_QTY, POST_DATE

10001, 10, 6, 01/14/2015

10001, 20, 5, 01/20/2015

10001, 30, 4, 01/15/2015

10001, 30, 4, 01/27/2015

10001, 40, 4, 01/28/2015

10002, 10, 6, 01/14/2015

10002, 20, 5, 01/20/2015

10002, 30, 4, 01/16/2015

10002, 30, 4, 01/27/2015

10002, 40, 4, 01/28/2015

];

NoConcatenate

Final:

Load DOC_NUM, FirstSortedValue(DOC_NUM_ID,POST_DATE) As DOC_NUM_ID, FirstSortedValue(RCV_QTY,POST_DATE) As RCV_QTY, Date(FirstSortedValue(POST_DATE,POST_DATE)) As POST_DATE

Resident Temp Group By DOC_NUM;

Concatenate(Final)

Load DOC_NUM, FirstSortedValue(DOC_NUM_ID,POST_DATE,2) As DOC_NUM_ID, FirstSortedValue(RCV_QTY,POST_DATE,2) As RCV_QTY, Date(FirstSortedValue(POST_DATE,POST_DATE,2)) As POST_DATE

Resident Temp Group By DOC_NUM;

Drop Table Temp;

Re: Script for below logic?

maxgro — Sun, 08 Mar 2015 08:42:14 GMT

using sunindia (thanks) data

Table:

LOAD * INLINE [

DOC_NUM, DOC_NUM_ID, RCV_QTY, POST_DATE

10001, 10, 6, 01/14/2015

10001, 20, 5, 01/20/2015

10001, 30, 4, 01/15/2015

10001, 30, 4, 01/27/2015

10001, 40, 4, 01/28/2015

10002, 10, 6, 01/14/2015

10002, 20, 5, 01/20/2015

10002, 30, 4, 01/16/2015

10002, 30, 4, 01/27/2015

10002, 40, 4, 01/28/2015

];

Right keep (Table)

First 2 load POST_DATE where isnull(Peek(POST_DATE)) or POST_DATE=Peek(POST_DATE)+1;

load POST_DATE, sum(1) Resident Table

group by POST_DATE

Order by POST_DATE;