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

[SunainaUmberkar]

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.

[canerkan · Accepted solution]

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

[gthuiz]

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;

[MarcoWedel]

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());

[SunainaUmberkar]

Hello Canerkan,

This has helped. It works as expected.

Thanks for the help.

[SunainaUmberkar]

Hello MarcoWedel,

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