13 Replies Latest reply: Aug 24, 2012 8:00 AM by Felim Shanaghy RSS

    Map Load If Function

      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
          Felim Shanaghy

          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

              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
                  Felim Shanaghy

                  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

                      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
                          Felim Shanaghy

                          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
                    Kourosh Karimi-Ghovanlou

                    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
                      Felim Shanaghy

                      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

                          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
                              Felim Shanaghy

                              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

                                  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