topic Re: Keep only the first record for a specific key in QlikView

Keep only the first record for a specific key

Mon, 04 Jul 2011 14:32:34 GMT

Hi,

I want to keep only the first record of an SQL file, when the key change

I use this script and it works fine. But i want to knows if i can do a better one (Try with "not exists" but don't seems to work)

Any suggestions

Thx

Works Ok :

TempArticles:

LOAD

Text(CodeArticle) as [%ArticleID],

Designation1 as [Désignation],

Designation2 as [Désignation 2]

;

SQL SELECT

CodeArticle,

Designation1,

Designation2

FROM ARTm;

// Tri de la table & Dédoublonage

TempArticles2:

NOCONCATENATE LOAD

if(%ArticleID=Previous(%ArticleID),null(),'1') as %1erEnr

RESIDENT [TempArticles] order by [%ArticleID];

DROP TABLE TempArticles;

Articles:

NOCONCATENATE LOAD * RESIDENT [TempArticles2] where %1erEnr;

DROP TABLE TempArticles2;

DROP FIELD %1erEnr;

Don't works :

Articles:

LOAD

Text(CodeArticle) as [%ArticleID],

Designation1 as [Désignation],

Designation2 as [Désignation 2]

where not exists(%ArticleID,CodeArticle);

SQL SELECT

CodeArticle,

Designation1,

Designation2

FROM ARTm;

Re: Keep only the first record for a specific key

nagaiank — Mon, 04 Jul 2011 21:37:44 GMT

I tried the following script and it loaded the first record only for each Key. If you replace the inline load to SQL SELECT statement to load from your SQL file and change the names of the key and data fields to the fieldnames in your SQL file, I think it should work.

Table1:

LOAD * Where Key <> Previous(Key);

LOAD * Inline [

Key, Data1, Data2

1,A,B

1,C,CD

2,D,E

2,E,F

2,F,G

3,1,2

3,2,3

3,3,4

];

Hope this helps.

Re: Keep only the first record for a specific key

Tue, 05 Jul 2011 08:17:38 GMT

Thanks. It is simpler. Here is my new code. I must use a resident table because my Articles Table is not sorted.

Do you know why the not exists clause don't works

regards

TempArticles:

LEFT KEEP (Lignes) LOAD

Text(CodeArticle) as [%ArticleID],

Designation1 as [Désignation],

Designation2 as [Désignation 2]

;

SQL SELECT

CodeArticle,

Designation1,

Designation2

FROM ARTm;

// Tri de la table & Dédoublonage

Articles:

NOCONCATENATE LOAD * RESIDENT [TempArticles] where %ArticleID<>Previous(%ArticleID) order by [%ArticleID];

DROP TABLE TempArticles;