Using ApplyMap() function

[#1]

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

[#2]

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.

[#3]

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

[#4]

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

[#5]

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.

.....

[#6]

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

[#7]

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.

[#8]

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         

[#9]

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

[#10]

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';

[#11]

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.