where not exists after a loop: no rows selected

Mon, 25 Jan 2016 11:44:43 GMT

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;

Re: where not exists after a loop: no rows selected

swuehl — Mon, 25 Jan 2016 12:38:33 GMT

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;

Re: where not exists after a loop: no rows selected

Mon, 25 Jan 2016 13:16:26 GMT

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

Solution verified, tested and approved.

Thanks!

topic Re: where not exists after a loop: no rows selected in QlikView

where not exists after a loop: no rows selected

Re: where not exists after a loop: no rows selected

Re: where not exists after a loop: no rows selected