topic Re: Where clause with Load Statement in App Development

How to use Where clause with Load Statement in Qlik Sense?

SunainaUmberkar — Wed, 02 Nov 2022 21:21:00 GMT

Hello All,

I am trying to use a where clause with Load Statement in Qliksense.

There are 2 Excels. 1st excel has 2 fields Transactions and Role. The 2nd excel consists of transactions that need not show in the dashboard. Earlier in this transaction we had manually mentioned in the where condition as

Load Transactions, Role from A where not match(Transactions,'1','2','3');

Now the transactions which to be excluded are there in Excel. Instead of manually excluding the transactions by providing in where condition, we need to load the excel with those transactions and exclude them.

I tried using inner join, left or Right join but none works.

Can anyone please assist with this? Thanks in advance.

Re: How to use Where clause with Load Statement in Qlik Sense?

canerkan — Wed, 02 Nov 2022 21:22:12 GMT

Hi SunainaUmberkar,

try loading the Excel file with the transactions that need to be excluded first like:

ExcludedTransactions: Load Distinct Transaction as exclude_Transaction From .....

Then Load the Table of all Transactions like this:

Transactions: Load Transaction, Role From... where not Exists(exclude_Transaction, Transaction); Drop Table ExcludedTransactions;

Let me know if it helped.

Regards

Can

Re: How to use Where clause with Load Statement in Qlik Sense?

gthuiz — Wed, 02 Nov 2022 21:21:47 GMT

You can do something like this:

//load transactions you want to exclude into mapping table Map_Include: Mapping LOAD TransactionKey AS %Key, 0 AS [Map Value] FROM [lib://Excel2.xlsx] (ooxml, embedded labels, table is Sheet1); //load all transactions into temp table tTransactions: LOAD TransactionKey AS %Key, ApplyMap('Map_Include',TransactionKey,1) AS _Include, Value FROM [lib://Excel1.xlsx] (ooxml, embedded labels, table is Sheet1); //reload tTransactions and only include ones that were not in the exclude list Transactions: Noconcatenate LOAD %Key, Value Resident tTransactions WHERE _Include = 1; Drop table tTransactions;

Re: Where clause with Load Statement

MarcoWedel — Mon, 07 Feb 2022 21:46:06 GMT

maybe using the ApplyMap exclusion already during the initial load:

mapExcel2: Mapping Load Transactions, False() Inline [ Transactions 1 2 3 ]; tabExcel1: Load * Inline [ Transactions, Role 1,Role1 2,Role2 3,Role3 4,Role4 5,Role5 ] Where ApplyMap('mapExcel2',Transactions,True());

Re: Where clause with Load Statement

SunainaUmberkar — Tue, 08 Feb 2022 13:41:32 GMT

Hello Canerkan,

This has helped. It works as expected.

Thanks for the help.

Re: Where clause with Load Statement

SunainaUmberkar — Tue, 08 Feb 2022 13:50:28 GMT

Hello MarcoWedel,

Thanks for the help. The solution provided by Canerkan has worked.