Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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?
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
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
];
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.
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.