Exists() dosent work

[#1]

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;

[sujeetsingh]

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;

[sujeetsingh]

Try using this

where (SubCode <> null()

[Gysbert_Wassenaar · Accepted solution]

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;

[swuehl]

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;

[#6]

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!

[Gysbert_Wassenaar]

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.