Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Mapping in expression

Hello *,

is it possible to do a mapping in an expression. My formula returns eg. value 1 to 25.
I'd like to do the mapping inside the expression not in the script.

Mapping table would look like this:

Map:
1, AA
2, AB
3, AX
....
25, TR

Tried with mapping without success 😞

Any ideas?
Thanks for your help.

5 Replies
MVP
MVP

Mapping in expression

Mapping tables are discarded at the end of the load, so you can't use them, and so you can't use applymap() to do this, even though it seems like the obvious solution. If Map is a regular table... I'm still not thinking of a good way to do it.

I am thinking of a bad way to do it, though, assuming you want to stick with editing a table and not an expression. Use a loop during the script to create a string like this and assign it to variable vMap:

pick($1,'AA','AB','AX',...,'TR')

Untested, but the script might look something like this (assuming your Map table is already loaded, was loaded in numeric sequence, no sequence values are skipped, and the resulting text is in field "Text"):

SET vMap='pick($1';
FOR I = 0 TO noofrows('Map')-1
LET vMap = vMap & ',' & chr(39) & peek('Text',I,'Map') & chr(39)
NEXT I
LET vMap = vMap & ')';

Then $(vMap(your formula)) should return the text corresponding to the number. If we're lucky.

There's almost certainly a cleaner, easier way.

mov
Esteemed Contributor III

Mapping in expression

I didn't try what I'm going to suggest, but hope it will work...

1. Create logical island, a table you'll use for mapping, but a permanent one:
A, B
1, AA
2, AB
3, AX
...

2. Use expression with set analysis on the front end:
only(${<A=${=your exprerssion}>}, B)
(There could be syntax nuances I can't predict here)

3. Let us know if it works... Smile

sparur
Valued Contributor II

Mapping in expression

Hello, ?olleagues.

I tested this approach (suggested by Michael). and I was very surprised, but it works (with small modifications in SET expression) 🙂

see in attachment.

MVP
MVP

Mapping in expression

Ah, but it only works if you have a single company selected. Set analysis is only evaluated once for the whole chart, not once for each row, causing the problem.

But that DOES imply that you could do it with a logical island and an IF:

only(if(A=your expression,B))

It works just fine.

But now I'm curious, since I don't have to make my own sample file. Yep, my approach worked just as written, once I added the missing semicolon at the end of the LET statement.

The only(if()) approach seems a lot better than mine, though, so that's what I'd use. It's also more robust, because it doesn't require that your mapping table be in sequential order and not skip any numbers. For that matter, it can be used with any sort of mapping, not just an integer to text map. All three approaches can be seen in the attached file.

Not applicable

Mapping in expression

Aloah,

You could also try to solve this with the replace function. I use a textobject to indicate which active selections have been made. I therefor use an inline table in the script:

Fieldname:
LOAD * INLINE [
String, To
Test.Field, Myalternativefieldname
];

In my textobject i use:

=replace(getcurrentselections(),Fieldname.String,Fieldname.To)

Now when users make a selection in Test.Field, my textobject will show the parameters as Myalternativefieldname: <parameters>.

I hope this is usefull to you.

Regards,

Patrick Tehubijuluw