Script for below logic?

[dmohanty]

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?

[rwunderlich]

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

[sunny_talwar]

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,

S

[anbu1984]

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;

[maxgro]

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;