topic Re: Duplicate Rows Elimination in QlikView

Duplicate Rows Elimination

naziralala — Sat, 31 Oct 2015 12:07:37 GMT

Hi,

I am faced with a problem of Duplicate records as below:

Main:

Book Id Vendor Code material Group

3456 212 10

6547 312 8

But when i join on another table with 3 records as below:

Temp:

AvCode material Group Mgroup desc Vendor Code

0 10 Rice 212

0 35 Bajra 212

1 85 Jowar 212

0 10 Rice 312

0 35 Bajra 312

0 85 Jowar 312

My need is to get only one record with joining on Vendor code. I tried using a key of material group and Vendor code, then I get blank AVCode.

Please assist.

Thanks in advance.

Nazira !

Re: Duplicate Rows Elimination

swuehl — Sat, 31 Oct 2015 12:13:54 GMT

Sorry, I don't understand your issue. Could you check your OP, I think you are missing the table with the 3 records you are joining.

Could you set up a working small QV script or sample QVW that demonstrates your issue?

Re: Duplicate Rows Elimination

naziralala — Sat, 31 Oct 2015 12:29:10 GMT

When i join Temp on Vendor Code, i get 3 records:

Main:

Book Id Vendor Code material Group AvCode

3456 212 10 0

3456 212 35 0

3456 212 85 1

Re: Duplicate Rows Elimination

MK_QSL — Sat, 31 Oct 2015 12:36:38 GMT

Main:

Load

[Vendor Code] & '|' & [material Group] as Key

Inline

[

Book Id, Vendor Code, material Group

3456, 212, 10

6547, 312, 8

];

Temp:

Load

AvCode, [Mgroup desc],

[Vendor Code] & '|' & [material Group] as Key

Inline

[

AvCode, material Group, Mgroup desc, Vendor Code

0, 10, Rice, 212

0, 35, Bajra, 212

1, 85, Jowar, 212

0, 10, Rice, 312

0, 35, Bajra, 312

0, 85, Jowar, 312

];

Re: Duplicate Rows Elimination

swuehl — Sat, 31 Oct 2015 12:38:54 GMT

Looks like you want to have a key made of material group and Vendor, why doesn't it work?

Main:

LOAD * INLINE [

Book Id, Vendor Code, material Group

3456, 212, 10

6547, 312, 8

];

Temp:

LEFT JOIN

LOAD * INLINE [

AvCode, material Group, Mgroup desc, Vendor Code

0, 10, Rice, 212

0, 35, Bajra, 212

1, 85, Jowar, 212

0, 10, Rice, 312

0, 35, Bajra, 312

0, 85, Jowar, 312

];

returns one line for Book Id 3456 and one line for Book Id 6456 (but for this, there is no match, so AvCode and Mgroup desc are NULL.

Re: Duplicate Rows Elimination

naziralala — Sat, 31 Oct 2015 15:09:52 GMT

Here is my script, as below:

Main:

LOAD [Sales Document],

[ABC Class]

FROM

... Main.QVD](qvd);

left join

[Temp]:

LOAD

[Vendor Code],

[Vendor Code] as [Vcode],

[Vendor Name],

[Vendor City]

FROM

...Temp.QVD(qvd);

Left Join

LOAD

[Vendor Code],

[Material Group],

[Material Group Description],

[Vendor Name] as [V name],

[AVCode]

FROM

..Products.QVD(qvd)

Where Exists([Vcode],[Vendor Code])

AND [Material Group]='10' ;

Now, I am getting the desired result...when i join on [Vendor Code] & '|' & [material Group] as Key

Thank You for all the guidance.