topic Re: Using ApplyMap() function in QlikView

Using ApplyMap() function

Wed, 04 Sep 2013 11:56:22 GMT

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

vikasmahajan — Wed, 04 Sep 2013 11:58:01 GMT

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

Should work.

Vikas

Re: Using ApplyMap() function

Gysbert_Wassenaar — Wed, 04 Sep 2013 12:02:43 GMT

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

Wed, 04 Sep 2013 12:03:23 GMT

Il me sort l'erreur "Champ non trouvé - <PARTY_NAME>"

Re: Using ApplyMap() function

Wed, 04 Sep 2013 12:07:09 GMT

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

Wed, 04 Sep 2013 12:08:37 GMT

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 — Wed, 04 Sep 2013 12:08:48 GMT

Hi,

Try this

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

Regards

Neetu

Re: Using ApplyMap() function

Wed, 04 Sep 2013 12:13:39 GMT

I get this error

Échec de lecture ODBC

while trying what you had suggested ...

Re: Using ApplyMap() function

Wed, 04 Sep 2013 12:29:44 GMT

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,

Re: Using ApplyMap() function

Wed, 04 Sep 2013 13:20:02 GMT

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

Wed, 04 Sep 2013 13:22:52 GMT

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

Wed, 04 Sep 2013 13:43:43 GMT

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

Wed, 04 Sep 2013 13:47:04 GMT

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,

Re: Using ApplyMap() function

Wed, 04 Sep 2013 13:58:09 GMT

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

Wed, 04 Sep 2013 14:15:49 GMT

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,

Re: Using ApplyMap() function

Wed, 04 Sep 2013 15:26:12 GMT

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

Wed, 04 Sep 2013 17:40:58 GMT

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

Thu, 05 Sep 2013 08:01:47 GMT

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

Thu, 05 Sep 2013 08:24:27 GMT

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

Thu, 05 Sep 2013 08:36:07 GMT

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.