Duplicate Rows Elimination

[naziralala]

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 !

[swuehl]

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?

[naziralala]

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

[MK_QSL]

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
];

[swuehl]

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.

[naziralala]

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.