topic Re: Exists() dosent work in QlikView

Exists() dosent work

Mon, 16 Dec 2013 09:12:08 GMT

I only want to get Tshirt3 in last tabel 'newProducts'. Ived tried this with Exists, looked in the manual and looked at exampels but i cant get it right. Any idees?

OldProducts:

Load * Inline [

product,date,sum

'Tshirt1',2013-09-01,290

'Tshirt1',2013-10-01,280

'Tshirt1',2013-11-01,260

'Tshirt1',2013-12-01,200

'Tshirt2',2013-09-01,300

'Tshirt2',2013-10-01,250

'Tshirt2',2013-11-01,200

'Tshirt2',2013-12-01,150

];

AllProducts:

Load * Inline [

product,date,sum,tmp

'Tshirt1',2013-09-01,290

'Tshirt1',2013-10-01,280

'Tshirt1',2013-11-01,260

'Tshirt1',2013-12-01,200

'Tshirt2',2013-09-01,300

'Tshirt2',2013-10-01,250

'Tshirt2',2013-11-01,200

'Tshirt2',2013-12-01,150

'Tshirt3',2013-12-29,150

];

Concatenate(AllProducts)

load *

resident OldProducts

;

newProducts:

load

product

,RowNo() as rowNo

resident AllProducts

where NOT Exists(product)

order by date ASC

;

Drop tables AllProducts, OldProducts;

Re: Exists() dosent work

sujeetsingh — Mon, 16 Dec 2013 09:16:37 GMT

I dont know ehat error you are getting but see the below sample and put this in new file it relaods.

Table1:

LOAD * INLINE [

Code, SubCode, Qty, Total Qty

111, , 25, 58

111, b, 23, 58

111, , 10, 58

222, a, 11, 68

222, b, 34, 68

333, c, 23, 68

];

Table2:

LOAD

Code, SubCode, Qty, [Total Qty] as ss

Resident Table1

where not Exists(SubCode)

Order by Code ASC;

Re: Exists() dosent work

sujeetsingh — Mon, 16 Dec 2013 09:20:27 GMT

Try using this

where (SubCode <> null()

Re: Exists() dosent work

Gysbert_Wassenaar — Mon, 16 Dec 2013 09:22:36 GMT

When you concatenate the old and new products you get a product field with all the products, i.e. all products exists. That's why the not exists(product) doesn't work. Try:

OldProducts:

Load product as oldproduct, date, sum Inline [

product,date,sum

'Tshirt1',2013-09-01,290

'Tshirt1',2013-10-01,280

'Tshirt1',2013-11-01,260

'Tshirt1',2013-12-01,200

'Tshirt2',2013-09-01,300

'Tshirt2',2013-10-01,250

'Tshirt2',2013-11-01,200

'Tshirt2',2013-12-01,150

];

AllProducts:

Load * Inline [

product,date,sum,tmp

'Tshirt1',2013-09-01,290

'Tshirt1',2013-10-01,280

'Tshirt1',2013-11-01,260

'Tshirt1',2013-12-01,200

'Tshirt2',2013-09-01,300

'Tshirt2',2013-10-01,250

'Tshirt2',2013-11-01,200

'Tshirt2',2013-12-01,150

'Tshirt3',2013-12-29,150

];

NewProducts:

noconcatenate

load * resident AllProducts

where not exists(oldproduct, product);

drop tables OldProducts, AllProducts;

Re: Exists() dosent work

swuehl — Mon, 16 Dec 2013 09:23:15 GMT

maybe like this:

OldProducts:

Load *,product as OldProduct Inline [

product,date,sum

'Tshirt1',2013-09-01,290

'Tshirt1',2013-10-01,280

'Tshirt1',2013-11-01,260

'Tshirt1',2013-12-01,200

'Tshirt2',2013-09-01,300

'Tshirt2',2013-10-01,250

'Tshirt2',2013-11-01,200

'Tshirt2',2013-12-01,150

];

AllProducts:

Load * Inline [

product,date,sum

'Tshirt1',2013-09-01,290

'Tshirt1',2013-10-01,280

'Tshirt1',2013-11-01,260

'Tshirt1',2013-12-01,200

'Tshirt2',2013-09-01,300

'Tshirt2',2013-10-01,250

'Tshirt2',2013-11-01,200

'Tshirt2',2013-12-01,150

'Tshirt3',2013-12-29,150

];

newProducts:

load

product

,RowNo() as rowNo

resident AllProducts

where NOT Exists(OldProduct,product)

order by date ASC

;

Drop tables AllProducts, OldProducts;

Re: Exists() dosent work

Mon, 16 Dec 2013 09:28:52 GMT

I get it, but why dose the manual says:

"exists(IDnr, IDnr) returns -1 (true) if the value of the field IDnr in the current record already

exists in any previously read record containing that field."

"exists (IDnr) is identical to the previous example."

you should not rename the field to "old"...

Thanks!

Re: Exists() dosent work

Gysbert_Wassenaar — Mon, 16 Dec 2013 09:35:28 GMT

if the value of the field IDnr in the current record already

exists in any previously read record containing that field

Previously read record does not just mean the records of the table that's currently being loaded, but any record of any table already loaded in the in-memory database.