topic more than one description for a 'unique' key in QlikView

more than one description for a 'unique' key

Sun, 26 Sep 2010 12:54:59 GMT

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.

more than one description for a 'unique' key

Anonymous — Sun, 26 Sep 2010 13:29:13 GMT

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.

more than one description for a 'unique' key

Sun, 26 Sep 2010 18:42:37 GMT

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;

more than one description for a 'unique' key

Anonymous — Sun, 26 Sep 2010 18:54:02 GMT

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.