Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Architect
Creator
Creator

Using xls file in 'Where Match' script function.

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)

gaurab_basu_0-1613046332738.png

and use .xls in the script.

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

 

@sunny_talwar @Peter_Cammaert 

Labels (1)
2 Solutions

Accepted Solutions
rubenmarin

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;

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
rubenmarin

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;

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

Architect
Creator
Creator
Author

@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.