How to load data from table only if column has match with column from previous table?

[#1]

Hi,

I'm creating a Qlikview app where I have to give insights in incoming calls and the associated SAP data.

Between a Call and the SAP data there is a reference, through a SAP Number.

The information with the Call are imported from a database. The SAP data is loaded from a Excel sheet (a dump from the database).

In the Qlikview app only the SAP Data which has a reference with a Call is relevant. Thus, it's basically not necessary to import the whole excel sheet.

In my Qlikview Script I have imported all the Calls and SAP data. The reference between the two tables is identified by a field: %SAP.ID

Relevant fields:

Melding.SAP Nummer = The SAP Number from the Call table.

SAP.Nummer  = The SAP number from the SAP table.

My question is as follows:

How can I load the data from my SAP excelsheet on the condition that there is a match between the SAP number (SAP.Nummer) and the call SAP number (Melding.SAP Nummer) in my script?

I attached the sample dashboard as an attachment.

[Anonymous · Accepted solution]

Hi Nazeem,

I believe the expression you are looking for is "Exists". You can look this up in the QlikView help files but here is the example code it gives:

Load Employee, ID, Salary from Employees.csv;

Load FirstName& ' ' &LastName as Employee, Comment from Citizens.csv where exists (Employee, FirstName& ' ' &LastName);

Only comments regarding those citizens who are employees are read.

[#3]

Hi @Adam Booth,

Your provided solution works! I had to use the function Exists().
In my example I had to extend my WHERE-clause with the Exists function like this:

[SAP]:
LOAD
  AutoNumber([SO: Nummer]) as %SAP.ID,
  [SO: Nummer]            as [SAP.Nummer],
  [SO: Status]            as [SAP.Status],
  Order                    as [SAP.Order],
  Omschrijving            as [SAP.Omschrijving],
  Num([Kosten Werkelijk])  as [SAP.Werkelijkse kosten],
  Gefact.                  as [SAP.Gefactureerd],
  IF([SO: Status] = 'Afgehandeld', 'Gefactureerd', 'Niet gefactureerd') as [SAP.Facturatie status],
  [Uren Besteed]          as [SAP.Uren Besteed]
FROM
[$(DATA_DIR)ZANALYSIS_PATTERN_3(1) 30-1.xlsx]
(ooxml, embedded labels, table is [ZANALYSIS_PATTERN_3(1)])
WHERE(Order <> 'Resultaat'  AND [SO: Status] <> 'Niet toegewezen' AND Exists([Melding.SAP Nummer], [SO: Nummer]));

I had to check if the values from [Melding.SAP Nummer] exists in [SO: Nummer], which is the SAP number.