Merry Christmas to QlikCommunity!
Is it possible to use functions in mapping tables which are going to be evaluated whenn using ApplyMap? I almost found nothing about this scenario and I tried all kind of variations but with no success.
Very simple example: In a table I have fields COMPANY and SALES_OFFICE. For certain values of COMPANY I want to have the first two characters of SALES_OFFICE, for certain values the last two characters and for all the others the whole string SALES_OFFICE (would be default mapping value). In reality the case is more complex but I wanted to simplify as much as possible.
In my trials I worked with something like that:
MAP_TABLE: LOAD * INLINE [ COMPANY,FUNCTION Company1,'left(SALES_OFFICE,2)' Company2,'left(SALES_OFFICE,2)' Company3,'right(SALES_OFFICE,2)' ]; DATA: LOAD COMPANY,SALES_OFFICE, Evaluate(ApplyMap('MAP_TABLE',COMPANY,SALES_OFFICE)) as MAPPED_SALES_OFFICE FROM ......;
I fiddled around with double quotes, variables etc. etc. but didn't get it to work. I'm not quite sure but I think I once saw a working example for a similar scenario long ago .... Any help appreciated.... For me it's important to know if this CAN be done in this way (with mapping) - I have some workarounds at hand but I don't want to use them if it can be done as suggested.
Thx for any suggestions,
You can achieve your result in the Inline Table using following code:
LOAD COMPANY, Evaluate(FUNCTION) as FUNCTION
(delimiter is ' ');
Here Delimiter is "Space".
After executing the above code, you will get your desired output from the Inline Table.
Output is attached in this reply.
After achieving this, you can use the Mapping Resident Load of this table and carry on further like below:
ApplyMap('MAP_TABLE',COMPANY,SALES_OFFICE) as MAPPED_SALES_OFFICE
Hope this will be useful.
Instead use simple method to achieve this. Use Pick(match( instead
Update: Captured else condition,
thanks for your suggestion, but I don't need the functions for the STRING 'SALES_OFFICE' but for the value of the field SALES_OFFICE - it worked of course with the string (right('SALES_OFFICE',2)) but NOT WITH right(SALES_OFFICE,2) - where SALES_OFFICE is the field in the data table.
thanks for your suggestion - this was one of the workarounds I had in mind but as said I would prefer a mapping as the structure seems clearer and more transparant for me - else I would have a very long expression with a lot of Companies - Company1 and Company2 were only example.
This would be a solution though if my idea won't work at all ....
Thx & rgds,
You can still use the same structure and make it pick(match statement dynamic like below
LOAD * INLINE [
Load 'Pick(match(COMPANY,'& Concat(chr(39)&COMPANY&chr(39),',')&')+1,'&
chr(10)&'SALES_OFFICE,'&Concat(chr(10)&FUNCTION,',')&')' as PickMatchExpr
let vPickMatchExpr = Peek('PickMatchExpr',0,'GenerateExpr');
Drop Table GenerateExpr;
Now you can use this variable vPickMatchExpr in your load statement like below and create a mapping field
$(vPickMatchExpr) as SALES_OFFICE_MAPPED
I haven't verified this, but maybe a dollar expansion of your mapping result could do the trick?
$(=ApplyMap('MAP_TABLE',COMPANY,SALES_OFFICE)) as MAPPED_SALES_OFF
I don't think evaluate function will work here and personally feel your scenario is not fit for using evaluate function. Evaluate function is useful for performing math operations like +,-,/, which takes the static text value and then evaluates the result. If you already having the Qlik function(In your case Left/Right) to do some string manipulation, there is no point on applying evaluate over it which also invalid .
Hence, Only way you can achieve this is using pick(match option.