Need Load script

[#1]

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 :- 

[its_anandrjs]

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

[MK_QSL · Accepted solution]

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;

[MarcoWedel]

Hi Reena,

Would you mind closing your threadNeed script before opening new threads for the same data.

Thanks

Marco

[#5]

Thank you...I got the solution...:)

[#6]

Thank you...I got the solution...:)

[MK_QSL]

Can you close the thread by selecting correct/helpful answer?

Thanks...

[its_anandrjs]

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