topic Re: Using xls file in 'Where Match' script function. in QlikView

Using xls file in 'Where Match' script function.

Architect — Thu, 11 Feb 2021 14:33:09 GMT

Hi team let me explain the scenario.

Below is my present logic-->

LOAD
YW4,
NAME,
Applymap('ABC_status',[ABC Status]) as [ABC Status]

FROM
$(vSupplyingPresentation)ABC\ABC_BASELOAD.qvd (qvd)
where match ([ABC Status],
'AA',
'AB',
'AC',
'AD'
)

Below is the proposed logic-->

instead of using

'AA',
'AB',
'AC',
'AD' as text in the script, we need to put them in one excel (path\ABC.xls)

and use .xls in the script.

Can anyone please help me here with how to achieve this APPROACH?

@sunny_talwar @Peter_Cammaert

Re: Using xls file in 'Where Match' script function.

rubenmarin — Fri, 12 Feb 2021 07:52:49 GMT

Hi @Architect, you can merge all values using Cocnat(), store in a variable, and use this variable on Match, like:

// Merge all values in one row tmpMatchStatus : LOAD Chr(39) & Concat(STATUS, Chr(39) &','& Chr(39)) & Chr(39) as MatchStatus From [excelFile]; // Store merged value ina variable LET vMatchValues=Peek('MatchStatus ',0,'tmpMatchStatus'); // Use variable on Match Data: LOAD ... Where Match(STATUS, $(vMatchValues)); DROP Table tmpMatchStatus;

Re: Using xls file in 'Where Match' script function.

marcus_sommer — Fri, 12 Feb 2021 09:31:29 GMT

An easier approach may be the following:

load Status as [ABC Status] from path\ABC.xls (biff, ...);

LOAD
YW4,
NAME,
Applymap('ABC_status',[ABC Status]) as [ABC Status]

FROM
$(vSupplyingPresentation)ABC\ABC_BASELOAD.qvd (qvd)
where exists([ABC Status]);

- Marcus

Re: Using xls file in 'Where Match' script function.

Architect — Fri, 12 Feb 2021 10:24:36 GMT

@marcus_sommerthank you for the suggestion.

Much appreciated.

Re: Using xls file in 'Where Match' script function.

rubenmarin — Fri, 12 Feb 2021 11:48:42 GMT

Yes @marcus_sommer, that's better, I don't know why I went the other way.

In fact I usually do an optimized exists load on fact tables to reduce data volume as you taught me a few years ago.