where not exists after a loop: no rows selected

[#1]

Hi all,

we're working on an AS400 environment and this means different libraries. As we use QV for overviews, these need to include data from multiple years.

Currently working on an account receivables QV and some invoices might still be outstanding during the 2015 cut off. So they are transferred to the 2016 library.

Now if I collect data from both 2015 and 2016, these invoices appear twice in my data model.

Below is how I worked and the issue I encountered is that the resident load doesn't give me any results. It always loads 0 rows.

Any insight would be greatly appreciated.

//Excelfile to compile a list of libraries to run through:

CompanyLibrary:

LOAD

    Library,

    Company,

    Period,

    Country,

    Compnr

FROM

[$(vDataLocation)Libraries - AR.xlsx]

(ooxml, embedded labels, table is BE);

     Let vCompanies = NoOfRows('CompanyLibrary');

     Let vConcatenate = 'LOAD';

For i=0 to $(vCompanies)-1

    Let vCompanyLibrary = Peek('Library', $(i), 'CompanyLibrary');

    Let vCompanyName =Peek('Bedrijf', $(i), 'CompanyLibrary');

    Let vCompanyCode = Peek('Firmanr', $(i), 'CompanyLibrary');

AR:

    LOAD

        *,

        ordno&'-'&fakno&'-'&klno&'-'&transtyp&'-'&dday&mnth&yyear as unique_key;

     SQL SELECT *

    FROM source.$(vCompanyLibrary).table;

next i;

//Finally, I reload the finished table to exclude doubles:

NoConcatenate

test:

LOAD

fakno,

unique_key

resident AR

where not exists(unique_key,unique_key);

drop table AR;

[swuehl · Accepted solution]

AR table already holds all unique key value, you would need something like

test:

LOAD

fakno,

unique_key,

unique_key as test

resident AR

where not exists(test ,unique_key);

DROP FIELD test;

[#3]

Ah... I was so close and yet so far away.

Solution verified, tested and approved.

Thanks!