Using xls file in 'Where Match' script function.

[Architect]

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 

[rubenmarin · Accepted solution]

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;

[marcus_sommer · Accepted solution]

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

[Architect]

@marcus_sommerthank you for the suggestion.

Much appreciated.

[rubenmarin]

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.