Calculated expressions (functions) in mapping load resp ApplyMap

[biester]

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,

Rgds,

Joachim

[rishikesh_suman]

Hello Joachim,

You can achieve your result in the Inline Table using following code:

MAP_TABLE:
LOAD COMPANY, Evaluate(FUNCTION) as FUNCTION

INLINE [
COMPANY FUNCTION
Company1 Left('SALES_OFFICE',2)
Company2 Left('SALES_OFFICE',2)
Company3 Right('SALES_OFFICE',2)
]

(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:

DATA:
LOAD
COMPANY,SALES_OFFICE,
ApplyMap('MAP_TABLE',COMPANY,SALES_OFFICE) as MAPPED_SALES_OFFICE
FROM ......;

Hope this will be useful.

Regards,
Rishikesh

[Kushal_Chawda]

Instead use simple method to achieve this. Use Pick(match( instead

=Pick(Match(COMAPNY,'Company1','Company2','Company1')+1,

SALES_OFFICE,

left(SALES_OFFICE,2),

left(SALES_OFFICE,2),

right(SALES_OFFICE,2))

Update: Captured else condition,

[biester]

Hello Rishikesh,

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.

Rgds,

Joachim

[biester]

Dear Kush,

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,

Joachim

[Kushal_Chawda]

You can still use the same structure and make it pick(match statement dynamic like below

MAP_TABLE:
LOAD * INLINE [
COMPANY,FUNCTION
Company1,'left(SALES_OFFICE,2)'
Company2,'left(SALES_OFFICE,2)'
Company3,'right(SALES_OFFICE,2)'
];

GenerateExpr:
Load 'Pick(match(COMPANY,'& Concat(chr(39)&COMPANY&chr(39),',')&')+1,'&
chr(10)&'SALES_OFFICE,'&Concat(chr(10)&FUNCTION,',')&')' as PickMatchExpr
Resident MAP_TABLE;

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

Data:

LOAD *,

          $(vPickMatchExpr) as SALES_OFFICE_MAPPED

FROM Table

[Vegar]

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

[biester]

Thanks, but that doesn't work either - the $-expanded expression seems to be skipped totally....

Rgds,

Joachim

[biester]

This works of course, but is another workaround and isn't a "real" mapping table anymore.

Thx & Rgds,

Joachim

[Kushal_Chawda]

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.