22 Replies Latest reply: Sep 5, 2013 5:56 AM by Hela REZGUI RSS

    Using ApplyMap() function

      Hey,

       

      I'm trying to use ApplyMap() function in Qlikview in order to retreive some data corresponding to an ID from another table.

      I have two tables "parties" and "orders". In orders, I have many fields like "Attribute0" which corresponds to the client's name. This attribute "Attribute0" can be numeric or alphanumeric.

       

      The table "parties" have two fields PARTY_ID and PARTY_NAME.

       

      My objective is to replace Attribute0 by the "PARTY_NAME" corresponding to the "PARTY_ID" when Attribute0=PARTY_ID everytime I have numeric value in Attribute0.

       

      I didn't know how to proceed ... I created two scripts:

       

      Mapping:

       

      Mapping:

      Mapping load

      PARTY_ID,

      PARTY_NAME;

       

      SQL SELECT distinct

      PARTY_ID,

      PARTY_NAME

      FROM hz_parties;

       

      Commande:

       

      load

        text(ORDER_NUMBER) as [NuméroCde],

        ATTRIBUTE0 as [ClientFinal],

        IF(ISNUM(ATTRIBUTE4),ATTRIBUTE4, ) AS PARTY_ID,

        IF (ISNUM(ATTRIBUTE4),APPLYMAP('Mapping', PARTY_ID)) as [ClientFinal];

       

      select distinct

        t1.ORDER_NUMBER ,

        t1.ATTRIBUTE0,

      from

      OE_ORDER_HEADERS_ALL t1;

       

      I have errors like "PARTY_ID uknown" while exeuting the script. Can any one help me to resolve this problem please ?

       

      Thanks

        • Re: Using ApplyMap() function
          vikas mahajan

          APPLYMAP('Mapping',PARTY_NAME)) as [ClientFinal];


          Should work.


          Vikas

          • Re: Using ApplyMap() function
            Gysbert Wassenaar

            You're loading only t1.ORDER_NUMBER and t1.ATTRIBUTE0 from OE_ORDER_HEADERS_ALL. So PARTY_ID is unknown. Same with ATTRIBUTE4 which you also don't load from that order headers table.

              • Re: Using ApplyMap() function

                Comment je peux charger PARTY_ID dans ce cas ? sachant que PARTY_ID correspond à ATTRIBUTE0 au cas ou ce dernier est numérique. Sinon Attribute4, est la même chose que Attribute0 ... C'est juste que je me suis trompée.

                • Re: Using ApplyMap() function

                  Here is the full script :

                  Cde:

                  load

                    text(ORDER_NUMBER) as [NuméroCde],

                   

                    ATTRIBUTE4 as [ClientFinal],

                   

                    IF(ISNUM(ATTRIBUTE4),ATTRIBUTE4, ) AS PARTY_ID,

                    IF (ISNUM(ATTRIBUTE4),APPLYMAP('Mapping', PARTY_NAME)) as [ClientFinal],

                    SOLD_TO_ORG_ID AS [ID_CLIENT],

                    HEADER_ID;

                    /*year(ORDERED_DATE) as [AnnéeCréationCde],

                    month(ORDERED_DATE) as [MoisCréationCde],

                    week(ORDERED_DATE) as [SemMoisCréationCde],

                    ORDERED_DATE as [DateCréationCdeOracle],*/

                    //Date(Date#(ATTRIBUTE1,'YYYY/MM/DD hh:mm:ss'),'YYYY/MM/DD') AS DateCdeClient,

                    //Date(Date#(ATTRIBUTE13,'YYYY/MM/DD hh:mm:ss'),'YYYY/MM/DD') As DateRéceptionCde,

                    //Date(Date#(ATTRIBUTE14,'YYYY/MM/DD hh:mm:ss'),'DD/MM/YYYY') As DateRéceptionFDTCdeADV;

                   

                  select distinct

                    //t1.ORDERED_DATE,

                    t1.ORDER_NUMBER ,

                    t1.ATTRIBUTE4,

                    SOLD_TO_ORG_ID,

                    t1.HEADER_ID,

                    t1.ORDERED_DATE,

                    t1.ATTRIBUTE1,

                    t1.ATTRIBUTE13,

                    t1.ATTRIBUTE14

                   

                  from

                  OE_ORDER_HEADERS_ALL t1;

                • Re: Using ApplyMap() function
                  Neetu Singh

                  Hi,

                   

                   

                  Try this

                   

                  IF (ISNUM(ATTRIBUTE4),APPLYMAP('Mapping', IF(ISNUM(ATTRIBUTE4),ATTRIBUTE4, ))) as [ClientFinal];

                   

                  Regards

                  Neetu

                  • Re: Using ApplyMap() function

                    It should be as easy as:

                     

                    Applymap ('Mapping', Attribute0, 'No result') as [ClientFinal];

                     

                    The syntax is:

                     

                    applymap ('name of your mapping load', the field in the table you want to apply the map to (in this case Attribute0), what should QlikView do if there is no match)     as Whatever you want to name your field

                    ; <-- close load or next field --> ,

                     

                     

                    BR,

                    JM

                      • Re: Using ApplyMap() function

                        Le problème est que les champs n'ont pas la même appellation, sinon, ça aurait pu être plus facile.

                         

                        J'arrive pas à résoudre le problème.

                          • Re: Using ApplyMap() function

                            Le problème est que les champs n'ont pas la même appellation, sinon, ça aurait pu être plus facile.

                             

                            J'arrive pas à résoudre le problème. Ce qui complique encore les chose, c'est que je dois aussi mettre une condition sur attribute4, s'il est numérique, je ferai le traitement, sinon, je garde la même valeur.

                              • Re: Using ApplyMap() function

                                Cde_temp:

                                LOAD

                                     Attribute0,

                                     text(ORDER_NUMBER)                          as [NuméroCde],    

                                     IF(ISNUM(ATTRIBUTE4),ATTRIBUTE4)      as PARTY_ID,

                                     SOLD_TO_ORG_ID                               as [ID_CLIENT],              

                                     HEADER_ID

                                ;

                                SELECT DISTINCT

                                  //t1.ORDERED_DATE,

                                  t1.ORDER_NUMBER ,

                                  t1.ATTRIBUTE4,

                                  SOLD_TO_ORG_ID,

                                  t1.HEADER_ID,

                                  t1.ORDERED_DATE,

                                  t1.ATTRIBUTE1,

                                  t1.ATTRIBUTE13,

                                  t1.ATTRIBUTE14

                                from

                                OE_ORDER_HEADERS_ALL t1;

                                 

                                Cde:

                                NOCONCATENATE

                                LOAD *,

                                     Applymap ('Mapping', PARTY_ID, 'No result') as [ClientFinal];

                                RESIDENT Cde_temp;

                                 

                                Cde2:

                                CONCATENATE (Cde)

                                LOAD *,

                                     Applymap ('Mapping', Attribute0, 'No result') as [ClientFinal];

                                RESIDENT Cde_temp;

                                DROP TABLE Cde_temp;

                                DROP FIELDS Attribute0, PARTY_ID;

                                 

                                 

                                Something like this?

                                Hope it helps.

                                 

                                BR,

                                JM

                          • Re: Using ApplyMap() function
                            Charles Crous

                            Hi Hela,

                             

                            It should read as follows:

                             

                            if(isnum(ATTRIBUTE4), applymap('Mapping', ATTRIBUTE4)) as [Client Final]

                             

                            With a mapping table Qlikview automatically references the first field in your mapping table and returns the corresponding value in the second field. So what you are asking is for Qlikview to 'lookup' the ATTRIBUTE4 value for a corresponding match in PARTY_ID (the first field) and to return the corresponding PARTY_NAME.

                             

                            Secondly, be sure to change the name used to e.g. [Client Final] as you have already named "ATTRIBUTE4 as [ClientFinal]" two rows up and you are not allowed to have two fields with the same name in a table.


                            Hope This Helps,

                            Charles

                              • Re: Using ApplyMap() function

                                Je pense que j'ai mal compris la fonction applymap().

                                 

                                Dans mon esprit, j'ai fait un mapping avec PARTY_ID et PARTY_NAME.

                                 

                                Ensuite, à chaque fois que ATTRIBUTE4 est numérique,  je le renomme en PARTY_ID (histoire d'avoir le même nom comme dans la table de mapping). C'est d'ailleurs pour cette raison, que j'ai gardé la même nomination ClientFinal.

                                 

                                C'était pour dire (et je pense que ça ne fonctionne pas de cette façon), que si je trouve un numérique, je vais faire ApplyMap() et remplacer par la valeur de PARTY_NAME et mettre le tout dans ClientFinal. et si c'est pas numérique, je le mettrai aussi dans ClientFinal.

                                 

                                .....

                                  • Re: Using ApplyMap() function

                                    Yea that's not how applymap works.

                                     

                                    What a mapping load does is load 2 fields from a table.

                                    Then whenever you need it, you can 'paste' the second field onto any table you like that has a corresponding field1.

                                     

                                    So for example:

                                     

                                    [Mapping_customer_name]:

                                    MAPPING LOAD

                                    customer_id,

                                    customer_name

                                    FROM customers_table;

                                     

                                    /////// Later on in the script you are loading orders

                                    Orders:

                                    LOAD

                                    order_id,

                                    customer_id,

                                    Applymap('Mapping_customer_name', customer_id,'no result') as customer,

                                    order_amount

                                    .....

                                    FROM orders_table;

                                     

                                    After you script has loaded ALL MAPPING LOAD TABLES will be thrown away!

                                    QlikView does not keep MAPPING tables in the datamodel.

                                     

                                    So in your specific case, if you want to put the party name in your table, and it is dependent on the field 'attribute4' with a dependency of being numerical, the correct syntax would be:

                                     

                                    IF (ISNUM(ATTRIBUTE4), Applymap('Mapping', ATTRIBUTE4 ,ATTRIBUTE4)) as [ClientFinal]


                                    This will apply the mapping load when Attribute4 is numerical, otherwise it will load the data in the field Attribute4, named as the field ClientFinal.


                                    BR,

                                    JM

                                      • Re: Using ApplyMap() function

                                        Si je comprends bien, IF (ISNUM(ATTRIBUTE4), Applymap('Mapping', ATTRIBUTE4 ,ATTRIBUTE4)) as [ClientFinal] va procéder comme suit: Si Attribute4 est numérique, donc on fait appel à la table de mapping pour remplacer ATTRIBUTE4 par la valaur correspondante PARTY_NAME (en tenant compte du fait que PARTY_ID=ATTRIBUTE4).

                                         

                                        Si je comprends bien aussi, la fonction ApplyMap() ne regarde pas les noms de champ ! Le premier champ c'est équivalent à PARTY_ID ? C'est bien ça ?

                                         

                                        Sinon, j'ai essayé d'écrire la fonction comme vous me l'avez indiqué, mais ça me donne de null pour les ATTRIBUTE4 numérique.

                                          • Re: Using ApplyMap() function
                                            Charles Crous

                                            Hi,

                                             

                                            Yes, that is correct. ApplyMap does not look at the field names, only the position is important (and the fact that the mapping table can only have two fields).

                                             

                                            The last part through me a bit...Are you saying that all the instances where the ATTRIBUTE4 field is populated with alphanumeric characters, you are getting output?

                                             

                                            Based on the following assumptions:

                                            1) That the mapping table it being populated and the that the PARTY_ID field only has numeric values in it

                                            2) That your "Cde" table is not being populated with values because the preceding SQL load has field names starting           with "t1."

                                             

                                            The following code should work...

                                             

                                            // it's a good idea to provide a more descriptive name for the mapping table

                                            // because you might have multiple tables later on

                                            partyMapping: mapping load

                                              PARTY_ID,

                                              PARTY_NAME

                                            ;

                                            SQL select distinct

                                              PARTY_ID,

                                              PARTY_NAME

                                            FROM 'hz_parties';

                                             

                                            Cde: load

                                              text(t1.ORDER_NUMBER) as NuméroCde,

                                              if(isnum(t1.ATTRIBUTE4), t1.ATTRIBUTE4) as PARTY_ID,

                                              applymap('partyMapping', t1.ATTRIBUTE4, t1.ATTRIBUTE4) as ClientFinal,

                                              SOLD_TO_ORG_ID as ID_CLIENT,

                                              t1.HEADER_ID as HEADER_ID

                                            ;

                                            // I have commented out the other fields that won't be used above

                                            SQL select distinct

                                              //t1.ORDERED_DATE,

                                              t1.ORDER_NUMBER ,

                                              t1.ATTRIBUTE4,

                                              SOLD_TO_ORG_ID,

                                              t1.HEADER_ID,

                                              //t1.ORDERED_DATE,

                                              //t1.ATTRIBUTE1,

                                              //t1.ATTRIBUTE13,

                                              //t1.ATTRIBUTE14

                                            FROM

                                            'OE_ORDER_HEADERS_ALL t1';

                                             

                                            Since my assumption is that PARTY_ID only holds numeric values means that where t1.ATTRIBUTE4

                                               is text it will not match up with the PARTY_ID field and t1.ATTRIBUTE4 will be used as is (instead of an error            message), so this should give you the desired outcome:

                                             

                                                        applymap('partyMapping', t1.ATTRIBUTE4, t1.ATTRIBUTE4) as ClientFinal         

                                              • Re: Using ApplyMap() function

                                                Il semble que si j'écris t1.**** dans la partie du load, elle n'est pas reconnue (il me sort l'erreur: t1.***** non reconnue).

                                                 

                                                Je pense que c'est logique car dans la partie load, il ne sait pas ce que c'est t1 (qui est définie dans la partie select).

                                                  • Re: Using ApplyMap() function
                                                    Charles Crous

                                                    That's interesting. I assumed that the qualified naming convention of tableName.fieldName would hold during a preceding load. Sorry, I haven't tried the scenario that you're currently attempting.


                                                    And what happens when you change the last part of the preceding load to this?

                                                    Cde: load

                                                        text(ORDER_NUMBER) as NuméroCde,

                                                        if(isnum(ATTRIBUTE4), ATTRIBUTE4) as PARTY_ID,

                                                        applymap('partyMapping', ATTRIBUTE4, ATTRIBUTE4) as ClientFinal,

                                                        SOLD_TO_ORG_ID as ID_CLIENT,

                                                        HEADER_ID

                                                    ;

                                                     

                                                    Or just save yourself the pain and just rename the fields to more meaningful names during the SQL load and use the new names in the second part, e.g.

                                                     

                                                    Cde: load

                                                      ...

                                                      applymap('partyMapping', [tempPartyID/Name], [tempPartyID/Name]) as ClientFinal,

                                                    ..

                                                    ;

                                                    SQL select distinct

                                                      ..

                                                      t1.ATTRIBUTE4 as [tempPartyID/Name],

                                                    ...

                                                    FROM

                                                    'OE_ORDER_HEADERS_ALL t1';

                                                      • Re: Using ApplyMap() function

                                                        Je mets en pièce jointe le fichier .qvw

                                                         

                                                        Est ce que vous pouvez regarder dans le script SVP ?

                                                         

                                                        Je ne comprends pas la raison pour laquelle j'ai pas un bon retour, c à d, là ou j'ai attribute4 numérique, il le change par la valeur "null" pourtant il est censé (d'après le script) le changer par la valeur de PARTY_NAME correspondante.

                                                         

                                                         

                                          • Re: Using ApplyMap() function
                                            Rajesh Vaswani


                                            Hi,

                                             

                                            Mapping is a keyword in QlikView. You are using the same for table name.

                                             

                                            Not sure if this is fine.

                                             

                                            thanks,

                                            Rajesh Vaswani

                                            • Re: Using ApplyMap() function

                                              Le problème a été résolu.

                                               

                                              Pour ceux qui sont intéressés, ApplyMap() ne regarde pas les noms de champ (c'était ma première faute car je voulais faire un renommage qui n'a pas marché).

                                               

                                              Cette fonction ne m'a pas donné par la suite les résultats attendus (il remplace par null au lieu de la valeur attendue). Le problème était dans la position de la déclaration de la table de Mapping.

                                               

                                              Le problème a été résolu quand j'ai placé la déclaration de la table de mapping juste avant la déclaration de ma table.

                                               

                                              PartyMapping:

                                              Mapping load

                                              PARTY_ID,

                                              PARTY_NAME;

                                               

                                              SQL SELECT

                                              PARTY_ID,

                                              PARTY_NAME

                                              FROM hz_parties;

                                               

                                              Cde:

                                              LOAD

                                               

                                              Mes paramètres;

                                              SELECT

                                              Mes paramètres;

                                               

                                              Je vous remercie tous pour votre réactivité. J'apprends beaucoup de choses avec vous.