Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a data set like this above. From this I need to pick only those DOC_NUM and DOC_NUM_ID,
What could be the LOAD Script for this?
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
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
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;
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;