topic Mapping in expression in QlikView

Mapping in expression

Wed, 07 Apr 2010 15:02:27 GMT

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.

Mapping in expression

johnw — Thu, 08 Apr 2010 00:49:21 GMT

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.

Mapping in expression

Anonymous — Thu, 08 Apr 2010 04:06:23 GMT

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...

Mapping in expression

sparur — Thu, 08 Apr 2010 08:27:37 GMT

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.

Mapping in expression

johnw — Thu, 08 Apr 2010 17:39:10 GMT

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.

Mapping in expression

Fri, 28 May 2010 13:20:27 GMT

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