topic Two different variables inside a wildmap in QlikView

Two different variables inside a wildmap

Fri, 04 Aug 2017 13:32:30 GMT

Hello,

I am using the following script to creat a wildmap iteration inside a dimension that list description of other lines inside the table:

wildmap:

load *,

rowno() as Label;

LOAD * INLINE [

Key, Label1

];

SET vMapKey=; // Initialize variable

SET vMapLabel=; // Initialize variable

SET comma=; // Initialize variable

SET esc = chr(01); // The character we will use to escape quotes

// Loop through the rows of the wildmatch table to build the expression string,

FOR i = 0 to NoOfRows('wildmap')-1

LET vMapKey = vMapKey & comma & $(esc) & peek('Key', i, 'wildmap') & $(esc);

LET vMapLabel = vMapLabel & comma & $(esc) & peek('Label1', i, 'wildmap') & $(esc);

SET comma = ','; // Next time through the loop, insert a comma

NEXT i

SET i=; // Drop unneeded variable

SET comma=; // Drop unneeded variable

// For this example, I'm going to keep the wildmatch table for illustrative purposes on the Sheet.

//DROP TABLE wildmap; // Drop the wildmatch table -- don't need it anymore

// Combine the key and label values, replacing the $(esc) char with real quotes, to build the expression

LET vMapExpr = replace('pick(wildMatch($1,$(vMapKey)), $(vMapLabel))', $(esc), chr(39));

SET vMapKey=; // Drop unneeded variable

SET vMapLabel=; // Drop unneeded variable

SET esc=; // Drop unneeded variable

//exit script;

LOAD *,

if(BANCOArrg='ALL',$(vMapExpr(vte.TASKNAME)),BANCOArrg) as BANCONuevo

Resident Horas;

drop Table Horas; rename Table t to Horas;

the problem I have is that I need to make to different wildmaps over the same dimension (the dimension I am evaluation being: vte.TASKNAME), in other words, I need to find: one, the client (BancoNuevo), listed inside the vte.TASKNAME and the product (PRODNuevo) also listed inside the same vte.TASKNAME. My problem is that when I do something like this:

LOAD *,

IF(PRODArrg='ALL',$(vMapExpr(vte.TASKNAME)),PRODArrg) as PRODNuevo,

if(BANCOArrg='ALL',$(vMapExpr(vte.TASKNAME)),BANCOArrg) as BANCONuevo

Resident Horas;

drop Table Horas; rename Table t to Horas;

it apply the wildmap to both the client and product, and I end up with products inside the client list and clients inside the product list. I've also tried to make an if of the two; like evaluate first this and if it is this, then evaluate this other one, but it didn't work...

Re: Two different variables inside a wildmap

effinty2112 — Fri, 04 Aug 2017 14:05:30 GMT

Hi Jesus,

I hope I understand what you're trying to achieve but maybe the Wildmatch and Pick functions are better suited to your needs.

Pick(WildMatch(TASKNAME,'*BANISTMO*','*BC*','*BOLE*','*BOLP*','*DAVIVIENDA*','*FACTURAN*','*INNOVA*','*LAFISE*','*LOLB*','*LOLBe*','*mtrx*','*PSE*','*SUFI*'),

'BANI','BC','BOLE','BANCAENLINEA','DAV','FACTURANET','FILIALESEXTERIOR','LAFISE','BANCAENLINEA','BOLE','BANCAMOVIL','PSE','SUFI')

will look in TASKNAME and return the element in the second list that corresponds to the position of the first mask that in found in TASKNAME.

i.e if '*BANISTMO*' is not found but'*BC*' is found the expression will return the second element in the second list - 'BC'.

You could write a similar expression to look for products.

Hope this helps.

Good luck

Andrew

Re: Two different variables inside a wildmap

Tue, 08 Aug 2017 17:44:05 GMT

This worked out perfectly! Thank you.

Re: Two different variables inside a wildmap

effinty2112 — Wed, 09 Aug 2017 14:31:24 GMT

Hi Jesus,

Glad I was able to help. If you're satisfied with my answer please mark it as correct and this will close the thread.

Kind regards

Andrew