topic Re: Remove Unwanted rows from Straight Table while loading in QlikView

Remove Unwanted rows from Straight Table while loading

PRS — Tue, 09 May 2017 18:55:53 GMT

Hi All,

Hope you are doing well.

I facing problem in below requirement.

i have created a Straight table like below.

So my requirement is to

Contract No	Desc	Unit No	Price
2	Desc2	U2	500
3	Desc3	U3	450
4	Desc4	U4	580
4	Desc44	U4	580
5	Desc5	U5	756
6	Desc6	U6	954
7	Desc7	U7	246
7	Desc7.1	U7	246
7	Desc77	U7	246
8	Desc8	U8	256
9	Desc9.9	U9	543
9	Desc9	U9	543
9	Desc9.1	U9	543
9	Desc79.2	U9	543
9	Desc9.3	U9	543
9	Desc99	U9	543

Here some of contract numbers are repeating because their descriptions are different and remaining all columns are same.

So, my requirement is to display only one row for each contract no which are loading first and to exclude remaining rows.

So output should be like below.

Contract No	Desc	Unit No	Price
2	Desc2	U2	500
3	Desc3	U3	450
4	Desc4	U4	580
5	Desc5	U5	756
6	Desc6	U6	954
7	Desc7	U7	246
8	Desc8	U8	256
9	Desc9.9	U9	543

I am attaching sample data QVW file for your reference.

Please help.

Thanks,

Raja

Re: Remove Unwanted rows from Straight Table while loading

sunny_talwar — Tue, 09 May 2017 18:59:36 GMT

Try this

ContractTable:

LOAD * INLINE [

Contract No, Desc, Unit No, Price

2, Desc2, U2, 500

3, Desc3, U3, 450

4, Desc4, U4, 580

4, Desc44, U4, 580

5, Desc5, U5, 756

6, Desc6, U6, 954

7, Desc7, U7, 246

7, Desc77, U7, 246

7, Desc7.1, U7, 246

8, Desc8, U8, 256

9, Desc9.9, U9, 543

9, Desc9.1, U9, 543

9, Desc99, U9, 543

9, Desc79.2, U9, 543

9, Desc9, U9, 543

]

WHERE NOT EXISTS([Contract No]);

Re: Remove Unwanted rows from Straight Table while loading

PRS — Wed, 10 May 2017 12:08:29 GMT

HI Sunny,

Thanks for your reply.

Your solution is working fine with inline data. But when work with actual fields which are coming from db, it is not working as expected.

I am loading following fields:CID, CCODE, Contract No, Package, Desc, Unit No, Price, StatDate etc.(w.r.to granularity i mentioned the fields)

And i am joining this fact table with another fact table(left keep) based on CID, CCODE, Contract No, Unit No

Can you pls suggest me how to achieve my requirement.

Thanks&Regards

Raja

Re: Remove Unwanted rows from Straight Table while loading

sunny_talwar — Wed, 10 May 2017 12:14:02 GMT

Do you have Contract No loaded before somewhere else in the script before you bring it here?

Re: Remove Unwanted rows from Straight Table while loading

PrashantSangle — Wed, 10 May 2017 12:16:33 GMT

Hi,

just one query on what basis you are selecting which description you have to use when there is 2 records for 1 contract no??

Regards,

Re: Remove Unwanted rows from Straight Table while loading

PRS — Wed, 10 May 2017 13:21:21 GMT

I have contractno in both fact tables along with some other common keys(CID, CCODE,Unit No).

So i am joining both tables(left keep) based on composite key of CID, CCODE, Contract No, Unit No

Re: Remove Unwanted rows from Straight Table while loading

PRS — Wed, 10 May 2017 13:44:43 GMT

the records are repeating because of different descriptions for one contract number and all other values are same.

and sorry for not mentioning one more point.

The contractno's are based on package(field).

If a contractno has different descriptions with same package the only any of record(if possible 1st record) should be loaded and to be shown in report(if there are 5 records for this scenario then only one record should be loaded)

And if a contractno has different descriptions with different descriptions then all records should be loaded(if there 5 records for this scenario then all 5 records should be loaded)

I thinks so u got it. please let me know if any further clarification required.

Thanks,

Raja