topic Re: Map Load If Function in QlikView

Map Load If Function

Fri, 24 Aug 2012 09:47:18 GMT

Hello All,

I have the following mapping load:

if((ApplyMap('Map_DATASOURCE', THEVALUE)) = 'SuperUser','No') as ENDUSEREDIT,

It simply takes 'THEVALUE' to the Map_DATASORCE, and then applies an if based on the returned value.

If the value is 'SuperUser' put 'No' as the data.

What I have been trying to do is the else part. I have the formattin gbut can only match the value to another value. I would like to say "else, store the original returned vaule"... Like this:

if((ApplyMap('Map_DATASOURCE', THEVALUE)) = 'SuperUser','No', ORIGINAL VALUE RETURNED FROM MAP) as ENDUSEREDIT,

I just do not know what ORIGINAL VALUE RETURNED FROM MAP should be.

I have a feeling this would work but it is probably not good for performance as it does two mappings:

if((ApplyMap('Map_DATASOURCE', THEVALUE)) = 'SuperUser','No',ApplyMap('Map_DATASOURCE', THEVALUE)) as ENDUSEREDIT,

Any help would be appreciated.

Regards,

Jim

Re: Map Load If Function

Fri, 24 Aug 2012 09:54:21 GMT

If you are using an If Statement you would be better off to base it on the core data and just applymap where needed i.e. in your if statement use the original data?

Re: Map Load If Function

kouroshkarimi — Fri, 24 Aug 2012 09:54:34 GMT

Could you use a variable? e.g:

Let v_map = ApplyMap('Map_DATASOURCE', THEVALUE);

if( '$(v_map)' = 'SuperUser', 'No' , '$(v_map)' ) as ENDUSEREDIT;

Re: Map Load If Function

Fri, 24 Aug 2012 10:10:53 GMT

Hello Felim,

The original data is just an ID which does not help. The values I need are in the map table so the map part is a must, surely? ie, you take the ID, look it up in the map, then bring back the value. I then do the if on this value. - cannot see any other way to do this other than a join... which also does not really help for the if.

Regards,

Jim

Re: Map Load If Function

Fri, 24 Aug 2012 10:13:47 GMT

Hello Kouroshkarimi,

This looks promising.

I will give this a go and let you know how I get on.

Regards,

Jim

Re: Map Load If Function

Fri, 24 Aug 2012 10:19:52 GMT

Your misunderstanding my point, basically you would use the ID´s in your if statement rather than using an apply map.

If (THEVALUE = 1 OR THEVALUE = 2,'No',ApplyMap('Map_DATASOURCE', THEVALUE)) AS ENDUSEREDIT

This avoids mapping twice

Re: Map Load If Function

Fri, 24 Aug 2012 10:36:30 GMT

Hmmm, dont seem to understand.

To do an if statement I need the values which can only come from grabbing via the map load. The values I need are not in the initial table hence the mapping load. The initial table only holds IDs. Not the values that I am interested in.

To do the if you suggest is not possible with the data...

So the If(THEVALUE = 1 or THEVALUE = 2 cannot be done as THEVALUE is only ID.

I think this makes sense?

Re: Map Load If Function

Fri, 24 Aug 2012 10:37:32 GMT

Hello Kouroshkarimi,

Having trouble adding the variable to the script. Have not dont this part before and cannot find where to add this to qv.

I am on version 9.

Any help would be appreciated.

Regards,

Jim

Re: Map Load If Function

Fri, 24 Aug 2012 10:48:08 GMT

No that makes no sense, you have the data infront of you. Doing it the way Kourosh is saying is basically doing what you have done but means you have to type less..

basically Apply Map is a lookup, it matches ID in both tables.. You dont know what ID's you need to set to "No"?

Re: Map Load If Function

Fri, 24 Aug 2012 10:50:10 GMT

Also if you want to use Kourosh's approach use: -

Put this before your load statement

Let v_map = 'ApplyMap('Map_DATASOURCE', THEVALUE)';

Use this in your load statement

if( '$(v_map)' = 'SuperUser', 'No' , '$(v_map)' ) as ENDUSEREDIT;

Re: Map Load If Function

Fri, 24 Aug 2012 10:54:04 GMT

Hello Felim,

I have the ID fields you are correct.

Say 1033459... n, could be any number of them. The value I am passing through the map is this ID. So i cannot do the if on that. This just does not make sense to do.

Say I parr ID 9, then ID 10 through the map. And this returns 'Jimmy', 'Thomas'.

I then do the if on the returned data, not the ID. So if 'Jimmy = SuperUser','Yes'.... I cannot do this without the map as I only have the ID fields available.

I hope this makes sense,

Jim

Re: Map Load If Function

Fri, 24 Aug 2012 11:10:16 GMT

Basically we are checking to see if Jimmy is a Superuser..

You will need to do more than one map to get the result you want.

First you will need to ApplyMap('MAP',ID,'Unknown') AS User

Next you will need to use this to return the users access, at this point it is becoming more complex. I would suggest left joining your data onto the main table and working from there with a seperate if statement field.

This would be the easiest solution.

Re: Map Load If Function

Fri, 24 Aug 2012 11:22:46 GMT

Hello Felim,

Lets simplify, the map does everything that is required. It gets the value I need to compare in the if statement. This is not the problem.

The problem is that should the if statement fail, I want to show the original value passed.

Lets look deeper:

if((ApplyMap('Map_DATASOURCE', MYID)) = 'SuperUser','No',Yes') as ENDUSEREDIT,

This will make a field in the table called ENDUSEREDIT that for each row that says 'No' or 'Yes' depending if the value returned from the map = 'SuperUser'. Very simple and works.

However, I do not want the 'Yes' part. I want the value returned from the map if the fail clause of the IF is called.

Example, this:

if((ApplyMap('Map_DATASOURCE', MYID)) = 'SuperUser','No','USE VALUE RETURNED FROM MAP') as ENDUSEREDIT,

Lets say the map returns 'Jimmy', the If should make ENDUSEREDIT = 'Jimmy'.

Lets say the map returns 'SuperUser', the If should make ENDUSEREDIT = 'No'.

Please let me know if this is helpful as I think we have a misunderstanding on what the problem is. Your help has been great so far though.

Best Regards,

Jim

Re: Map Load If Function

Fri, 24 Aug 2012 11:59:56 GMT

right, then parsing the applymap twice is the easiest way and tbh ApplyMap isnt too inefficient. Alot of confusion hehe, its a friday!