Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
t:
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:
t:
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...
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
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
This worked out perfectly! Thank you.
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