topic Re: Need Load script in QlikView

Need Load script

Fri, 28 Mar 2014 08:54:33 GMT

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

Re: Need Load script

its_anandrjs — Fri, 28 Mar 2014 09:30:25 GMT

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

Re: Need Load script

MK_QSL — Fri, 28 Mar 2014 09:31:47 GMT

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;

Re: Need Load script

MarcoWedel — Fri, 28 Mar 2014 10:22:46 GMT

Hi Reena,

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

Thanks

Marco

Re: Need Load script

Sat, 29 Mar 2014 11:48:01 GMT

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

Re: Need Load script

Sat, 29 Mar 2014 11:48:10 GMT

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

Re: Need Load script

MK_QSL — Sat, 29 Mar 2014 11:58:31 GMT

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

Thanks...

Re: Need Load script

its_anandrjs — Sat, 29 Mar 2014 12:47:17 GMT

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