Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

23 Replies
vikasmahajan
Champion
Champion

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


Should work.


Vikas

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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;

neetu_singh
Partner
Partner

Hi,

Try this

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

Regards

Neetu

Not applicable
Author

I get this error

Échec de lecture ODBC

while trying what you had suggested ...

Not applicable
Author

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

Not applicable
Author

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.