Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
APPLYMAP('Mapping',PARTY_NAME)) as [ClientFinal];
Should work.
Vikas
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.
Il me sort l'erreur "Champ non trouvé - <PARTY_NAME>"
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.
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;
Hi,
Try this
IF (ISNUM(ATTRIBUTE4),APPLYMAP('Mapping', IF(ISNUM(ATTRIBUTE4),ATTRIBUTE4, ))) as [ClientFinal];
Regards
Neetu
I get this error
Échec de lecture ODBC
while trying what you had suggested ...
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
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.