Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Greetings for the day.
I want to load data as follows. Please let me know the LOAD script. Please find the sample data below.
LOGIC :-
I want records, group by PART NO, ORDER NO and CUST NO and I should remove records where LATEST_ETD is equal to "99999999" if that LATEST_ETD is first records for that group.
EG :- FOR PART NO (12345), ORDER NO(A), CUST NO(ABC), first two LATEST_ETD records are "99999999". So i Want to eliminate this records. But FOR PART NO (6789), ORDER NO(B), CUST NO(DEF), "99999999" is not first records. So I don't want to eliminate this record. Here Order by depends on CRE_DT field.
Finally My output should be the records mentioned in RED Color.
Please let me know the solution. Thanks in advance.
SAMPLE DATA :-
Try this....
Temp:
Load *, PART_NO&ORDER_NO&CUST_NO as Key Inline
[
PART_NO, ORDER_NO, CUST_NO, LATEST_ETD, CRE_DT
12345, A, ABC, 99999999, 01/03/2014
12345, A, ABC, 99999999, 02/03/2014
12345, A, ABC, 1457896, 12/03/2014
6789, B, DEF, 456987, 13/03/2014
6789, B, DEF, 99999999, 14/03/2014
6789, B, DEF, 64613513, 15/03/2014
9784, C, GHI, 99999999, 16/03/2014
9784, C, GHI, 99999999, 17/03/2014
9784, C, GHI, 546441131, 18/03/2014
];
Join
Load
Key,
FirstSortedValue(LATEST_ETD, CRE_DT) as FIRSTETD
Resident Temp
Group By Key;
NoConcatenate
FINAL:
Load
PART_NO,
ORDER_NO,
CUST_NO,
LATEST_ETD,
CRE_DT
Resident Temp
Where FIRSTETD & LATEST_ETD <> '9999999999999999'
Order By PART_NO,ORDER_NO,CUST_NO,CRE_DT;
Drop Table Temp;
Hi,
Check below code and you get your final result which is in red lines only
Temp:
LOAD PARTNO&'|'&ORDERNO&'|'&CUSTNO&'|'&LATEST_ETD as Key,PARTNO,ORDERNO,CUSTNO,LATEST_ETD,CRE_DT;
LOAD * Inline
[
PARTNO,ORDERNO,CUSTNO,LATEST_ETD,CRE_DT
12345, A, ABC, 99999999, 1/3/2014
12345, A, ABC, 99999999, 2/3/2014
12345, A, Anc, 1457395, 1213/2014
5739, H, DEF, 455937, 13/03/2014
5739, H, DEF, 99999999, 14/03/2014
5739, H, DEF, 54513513, 15/03/2014
9374, c, GHI, 99999999, 15/03/2014
9374, c, GHI, 99999999, 17/03/2014
9374, c, GHI, 545441131, 13/03/2014
];
Join
LOAD
Key,
Count(Key) as Ckey
Resident Temp
Group By Key;
Noconcatenate
T2:
LOAD PARTNO,ORDERNO,CUSTNO,LATEST_ETD,CRE_DT,Ckey
Resident Temp
Where Ckey =1;
DROP Table Temp;
Hope this helps
Thanks & Regards
Try this....
Temp:
Load *, PART_NO&ORDER_NO&CUST_NO as Key Inline
[
PART_NO, ORDER_NO, CUST_NO, LATEST_ETD, CRE_DT
12345, A, ABC, 99999999, 01/03/2014
12345, A, ABC, 99999999, 02/03/2014
12345, A, ABC, 1457896, 12/03/2014
6789, B, DEF, 456987, 13/03/2014
6789, B, DEF, 99999999, 14/03/2014
6789, B, DEF, 64613513, 15/03/2014
9784, C, GHI, 99999999, 16/03/2014
9784, C, GHI, 99999999, 17/03/2014
9784, C, GHI, 546441131, 18/03/2014
];
Join
Load
Key,
FirstSortedValue(LATEST_ETD, CRE_DT) as FIRSTETD
Resident Temp
Group By Key;
NoConcatenate
FINAL:
Load
PART_NO,
ORDER_NO,
CUST_NO,
LATEST_ETD,
CRE_DT
Resident Temp
Where FIRSTETD & LATEST_ETD <> '9999999999999999'
Order By PART_NO,ORDER_NO,CUST_NO,CRE_DT;
Drop Table Temp;
Hi Reena,
Would you mind closing your threadNeed script before opening new threads for the same data.
Thanks
Marco
Thank you...I got the solution...:)
Thank you...I got the solution...:)
Can you close the thread by selecting correct/helpful answer?
Thanks...
Hi,
If you got correct answer so mark this thread as a correct or Helpful so others can get the reference of this.
Thanks & Regards