more than one description for a 'unique' key

[#1]

Hello,

I have a problem with linking two tables.
In the first table I have a table with unique productkeys. In the other table I have a table with productkeys and descriptions of the keys.
For some productkeys there are more than one description.

eg:
Table1
productkey
1
2
3

Table2
productkey description
1 code1
1 code 1
2 code2
2 code_2
3 code3

I would like to have as a result:

productkey description
1 code1
2 code2
3 code3

How can I link the two tables so that I keep a 'unique productkey'. So if more than one description exists, I want to keep only the first descripton.

I've tried things with Load Distinct; left join; etc. But that gives not a good result.

[Anonymous]

The simpliest way, I think, is this:

LOAD DISTINCT
 productkey,
 maxstring(description) as description
RESIDENT Table2
GROUP by productkey;

It will give you one description per product key. I can't tell if it will be "the first descripton" - it depends on what you mean by this.

[#3]

this works for the 2 table I have with articlecode desciption.
But for the articlegroups I have the same problem. Therefor I used also 'Hierarchy' to be able to make a tree structure.
But the code you give me to keep only one description doesn't work here. Can you say me where I make a mistake.

Artikeltree:

HIERARCHY
( %Key_Product_groep, artparent, artikelgroepoms, , artikelgroepoms , Path)

LOAD
artgrp as %Key_Product_groep,
left(artgrp,(4*(niv-1))) & repeat( '0000', 8-niv+1) as artparent,
num(left(artgrp,4)) & '.' & if((num(mid(artgrp,5,4)))<>0,num(mid(artgrp,5,4)))& '.' & if((num(mid(artgrp,9,4)))<>0,num(mid(artgrp,9,4)))& '.' & if((num(mid(artgrp,13,4)))<>0,num(mid(artgrp,13,4))) & '.' & if((num(mid(artgrp,17,4)))<>0,num(mid(artgrp,17,4)))& '.' & if((num(mid(artgrp,21,4)))<>0,num(mid(artgrp,21,4)))& '.' & if((num(mid(artgrp,25,4)))<>0,num(mid(artgrp,25,4)))& '.' & if((num(mid(artgrp,29,4)))<>0,num(mid(artgrp,29,4))) & '.' & omschr1 as artikelgroepoms

FROM
..\data\QVD\artgrp.qvd
(qvd);

Artikeltree1:
LOAD DISTINCT
%Key_Product_groep,
artparent,
MaxString(artikelgroepoms) as artikelgroepoms,
artikelgroepoms1,
artikelgroepoms2,
artikelgroepoms3,
artikelgroepoms4,
artikelgroepoms5,
artikelgroepoms6,
artikelgroepoms7,
Path

RESIDENT Artikeltree
GROUP by %Key_Product_groep;

DROP TABLE Artikeltree;

[Anonymous]

The last table Artikeltree1 is technically incorrect. When you use GROUP BY, every field must be either in GROUP BY, or be aggregated. Or possibly you need to load only %Key_Product_groep and artikelgroepoms from Artikeltree, as you are doing now but without all the otehr fields, and after that join the other fields from the same Artikeltree, using %Key_Product_groep.
I cannot tell more without knowing the specifics. anyway, your second post is a different question, and I recommend you to start a new post about it.