Exists()

En riktigt bra script-funktion i Qlik är exists() – en funktion som kollar om ett värde finns inläst i datamodellen sedan innan. Om värdet finns i datamodellen returneras True och annars False. I det här inlägget ska vi gå igenom hur funktionen fungerar och vilka fallgropar som finns.

Grunderna
När man läser in data från en datakälla läses rad för rad. En rad kan t.ex. vara en rad i en Excel-fil eller en rad i en tabell i en databas. Raderna består av värden i kolumner och som utvecklare har vi möjligheten att skriva kod som styr vilka rader vi vill läsa in. Ett enkelt exempel är att vi bara vill läsa data från året 2022 – vi lägger då till en så kallad where-sats som filtrerar År=2022.

Load
	*
from [lib://DataFiles/Data.qvd] (qvd) where År=2022;

Ibland vill man styra så att vi läser rader med värden i en kolumn som antingen finns eller inte finns sedan tidigare i appen. Detta kan t.ex. vara användbart för att filtrera så att vi endast läser in artiklar i artikelregistret som motsvarar de artiklar som finns i kundorder.

Artikelregister består ofta av ett stort antal artiklar. En del av dessa artiklar säljs aldrig – det kan exempelvis handla om ingående material till tillverkning eller indirekt material som kaffe till kaffemaskinen. Dessa artiklar vill vi inte ska visas i vår försäljningsanalys – vi säljer dem aldrig.

Invoices:
Load
	InvoiceNo,
	InvoiceDate,
	ItemNo,
	InvoiceAmount
from [lib://DataFiles/InvoiceData.qvd] (qvd);

Items:
Load
	ItemNo,
	Description
from [lib://DataFiles/Items.qvd] (qvd) where exists(ItemNo);

I det här fallet använder vi exists() med en parameter exists(ItemNo). En parameter innebär att för varje rad data funktionen läser så jämförs värdet i kolumnen ItemNo med tidigare inläst data i ItemNo.

Det spelar ingen roll i vilka tabeller vi läst in ItemNo – värdet på en rad jämförs med alla värden som ItemNo har i alla tabeller i datamodellen.

I nedan video visas hur data läses in

Invoices:
Load
	InvoiceNo,
	ItemNo
from [lib://DataFiles/Invoices.qvd] (qvd);

Items:
Load
	ItemNo,
	Description
from [lib://DataFiles/Items.qvd] (qvd) where exists(ItemNo);

Utökad syntax

Ibland heter fältet som finns sedan tidigare i datamodellen något annat än det fält som man nu läser in. I de fallen kan man använda en utökad syntax för exists() som tar två parametrar.

Invoices:
Load
	InvoiceNo,
	InvoiceDate,
	ProductNo,
	InvoiceAmount
from [lib://DataFiles/InvoiceData.qvd] (qvd);

Items:
Load
	ItemNo as ProductNo,
	Description
from [lib://DataFiles/Items.qvd] (qvd) where exists(ProductNo, ItemNo);

Här kan vi se att vi läser in tabellen Items. På varje rad finns ett artikelnummer i ItemNo. Vi vill se om värdet i ItemNo finns sedan tidigare i datamodellen i fältet ProductNo.

Observera att även om vi döper om fältet ItemNo till ProductNo så är det fortfarande ItemNo vi ska använda i exists(). Det är namnet på fältet i datakällan vi anger.

Text och tal
Som ofta när man jämför värden i Qlik är det viktigt att skilja på text och tal. Ett tal kan oftast inte jämföras med en text och detta gäller även för exists()-funktionen. Ett tal lagrat som en text ser nästan lika ut vilket ibland gör det svårt att veta om en koppling fungerar eller ej. I exemplet nedan kommer inte ItemNo i Items kunna jämföras med ItemNo i Invoices.

Invoices:
Load
	InvoiceNo,
	InvoiceDate,
	ItemNo, // ItemNo stored in a text field
	InvoiceAmount
from [lib://DataFiles/InvoiceData.qvd] (qvd);

Items:
Load
	ItemNo, // ItemNo stored in a numeric field
	Description
from [lib://DataFiles/Items.qvd] (qvd) where exists(ItemNo);

Så hur vet vi om ett fält är lagrat som text eller tal? Det säkraste sättet är att se hur fältet klassificeras i datamodellvyn. Markera fältet och se vad det står på Tags. Om det står $text är fältet av typen text och om det står $numeric är fältet av typen tal. Om det står både $text och $numeric är fältet en mix av båda och det är inte säkert att kopplingar kommer att fungera.

Hur löser vi då problemet om våra fält är olika? Vi omvandlar värdena till text genom att använda text()-funktionen.

Invoices:
Load
	InvoiceNo,
	InvoiceDate,
	ItemNo,
	InvoiceAmount
from [lib://DataFiles/InvoiceData.qvd] (qvd);

Items:
Load
	text(ItemNo) as ItemNo,
	Description
from [lib://DataFiles/Items.qvd] (qvd) where exists(ItemNo, text(ItemNo));

Not Exists()
Man kan också vända på funktionen och kontrollera om värden inte finns inlästa sedan tidigare. Här finns det dock en fallgrop man bör vara uppmärksam på – när vi läser in vår nya data kommer den också att läggas till i listan på inlästa värden. Det kan göra att vi missar rader. Enklast är att visa det i en video nedan. Här vill vi läsa in artiklar, men inte de artiklar vars artikelgrupp (ItemGroupNo) finns i en Excel-fil. Vi använder med andra ord Excel-filen för att filtrera bort vissa artikelgrupper som inte är intressanta för mätningen.

ItemGroupsNotToRead:
Load
	ItemGroupNo
from [lib://DataFiles/ItemsNotToRead.xlsx]
(ooxml, embedded labels, table is [Item Groups]);

Items:
Load
	ItemNo,
	Description,
	ItemGroupNo
from [lib://DataFiles/Invoices.qvd] (qvd) where not exists(ItemGroupNo);

För att lösa detta måste vi ha olika namn på fältet vi filtrerar mot och det fält vi läser in data till. Detta kan enkelt lösas med en omdöpning och att använda den utökade syntaxen av exists().

Nedan döper vi om ItemGroupNo till ItemGroupNo_Filter. Vi kontrollerar sedan ItemGroupNo i Items mot det omdöpta fältet. På detta sätt lägger vi inte till nya värden i filterfältet när vi läser in artiklarna.

ItemGroupsNotToRead:
Load
	ItemGroupNo as ItemGroupNo_Filter
from [lib://DataFiles/ItemsNotToRead.xlsx]
(ooxml, embedded labels, table is [Item Groups]);

Items:
Load
	ItemNo,
	Description,
	ItemGroupNo
from [lib://DataFiles/Invoices.qvd] (qvd) 
where not exists(ItemGroupNo_Filter, ItemGroupNo);