Where Not Exists()

[Sebastian_Dec]

Hi, I want to split the data according to the value of the ArtId column.

I need one table where ArtId DOS-COD is equal to 1000 and another table with all rest unique ArtIds. Unfortunately, the Where Not Exists() function does not want to work for me, the ArtId_NotIn1000 table still returns all 57000 ArtIds.

For example, if the Faktura table has 57000 records for ArtIs, the Dossie1000_tmp table has 22000 records, the ArtId_NotIn1000 table should have 57000 - 22200 -> 35000 records.

The Invoice table actually has 1'000'000 records because many ArtIds are duplicated along with "DOS-COD" and "COT-COD", I cannot separate them on the basis of DOS-COD = 1000 and DOS-COD <> 1000. First I need to get the ArtId for DOS-COD = 1000, and then the rest which are unique so as not to be repeated.

My current code, please help:

Dossie1000_tmp:
Load
Distinct ArtId,
"DOS-COD",
"COT-COD"
Resident Faktury
Where "DOS-COD" = 1000;

NoConcatenate
ArtId_NotIn1000:
Load
Distinct ArtId,
"DOS-COD", 
"COT-COD" 
Resident Faktury
Where not Exists(ArtId, 'Dossie1000_tmp');

[PrashantSangle · Accepted solution]

Hello,

Where not Exists(ArtId, 'Dossie1000_tmp');

2nd parameter is incorrect. Please update that.

it is something like

where not exists(ArtId, ArtId)

[Sebastian_Dec · Accepted solution]

A fragment of my code that works.
1. You need to combine ArtId with maximum dates
2. leave only unique ArtId based on max dates
3. Filter the data in the table on Dossie 1000
4. Delete unnecessary tables because the exist function unwinds all existing tables in Qlik.
5. Leave the table with Dossie 1000, get the data from the first joins where ArtId and Max(data) are and use the Where Not Exists() function

The Where Not Exists() function cannot be targeted to a specific table.

Dossie1000_tmp_tmp: 
load 
    ArtId, 
    "DOS-COD", 
    "COT-COD", 
    NumDate 
Resident Faktury;

Drop Table Faktury;

inner join(Dossie1000_tmp_tmp) 
load 
	ArtId, 
    max(NumDate) as NumDate 
    resident Dossie1000_tmp_tmp 
group by ArtId;


Dossie1000_tmp:
Load
    FirstValue(ArtId) as ArtId,
    "DOS-COD",
    "COT-COD",
    NumDate
Resident Dossie1000_tmp_tmp
Group By NumDate, "COT-COD", "DOS-COD"
;

NoConcatenate
Dossie1000:
Load
    ArtId,
    "DOS-COD",
    "COT-COD"
Resident Dossie1000_tmp_tmp
Where "DOS-COD" = 1000 ;
;

STORE Dossie1000_tmp_tmp INTO [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Dossie1000_tmp_tmp.qvd]
(qvd);
STORE Dossie1000 INTO [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Dossie1000.qvd]
(qvd);

Drop Table Dossie1000_tmp_tmp;
Drop Table Faktury_Calosc;

NoConcatenate
ArtId_NotIn1000:
Load
    Distinct ArtId,
    "DOS-COD",
    "COT-COD"
FROM [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Dossie1000_tmp_tmp.qvd]
(qvd)
Where not Exists(ArtId);