topic Re: How to load data from table only if column has match with column from previous table? in QlikView

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

Wed, 12 Nov 2014 11:17:55 GMT

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.

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

Anonymous — Wed, 12 Nov 2014 11:45:20 GMT

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.

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

Thu, 13 Nov 2014 09:55:58 GMT

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.