Keep only the first record for a specific key

[#1]

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;

[nagaiank]

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.

[#3]

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;