ApplyMap

[#1]

Hi

i am try to do a vertical look up from a file xls to my Qlikview data.

The AlsisTable part work fine, but CustomerTable not, script run said that "CustomerName" not found

I tried to remove text or/and trim but it not work...

Some idea?

AlsisTable:

MAPPING LOAD text(trim(Alsis)),

     text(trim(AlsisDescription))

FROM

[Sources\ALSISdescription.xlsx]

(ooxml, embedded labels, table is Sheet1);

//Load the table to have AnytimeSupplier list

CustomerTable:

MAPPING LOAD text(trim(CustomerName)),

     text(trim(Anytime))

FROM

[Sources\AnytimeCustomerName.xlsx]

(ooxml, embedded labels, table is Sheet1);

Alonte:

//ODBC CONNECT TO AirArtecSu (XUserId is HcHXEJVMQbbEHaVd);

$(Include=include/connect_ITALWS0023_ArtecAirSU.conn)

LOAD *, week(Date) as Week,if(OrderNumber = peek(OrderNumber),0,1) as CountOrder,if(match(AlsisScope, 'J') or match(AlsisScope, 'G') , 'Yes', 'No') as PotentialIECom,TransfertPrice*Quantity as TransferPriceOrderQTY,

'Alonte' as Supplyer, ApplyMap('AlsisTable', text(trim(Alsis)),'Missing') as AlsisDescription,ApplyMap('CustomerTable', text(trim(CustomerName)),'No') as AnytimeCustomertimeCustomer;

SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Description,artikelvariant as ItemID,extordtyp,ordantal as Quantity,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,"alfa_alsis_product" as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr

FROM ArtecAirSU.dbo.orp;

Inner Join......

[Gysbert_Wassenaar · Accepted solution]

The second parameter in the applymap function should be a field (or expression based on fields) from the source table. In this case that means a field that the SQL SELECT statement returns. I don't see any field with the name CustomerName selected from the table ArtecAirSU.dbo.orp

[Agis-Kalogiannis]

You should name the fileds you're loading in the mapping tables by susing AS statements and then use the fieldnames in your applymap function, instead of an expression.

e.g.

AlsisTable:

MAPPING LOAD text(trim(Alsis)) AS Alsis,

     text(trim(AlsisDescription)) AS AlsisDescription

FROM

[Sources\ALSISdescription.xlsx]

(ooxml, embedded labels, table is Sheet1);

and in your applymap:

ApplyMap('AlsisTable', Alsis,'Missing') as AlsisDescription

Of course the Alsis field is a field that must exist in your Alonte table

Thanks

[Gysbert_Wassenaar]

The names in the mapping table are irrelevant. The only thing that matters is that the mapping table contains two fields. The first field will be used for the look up, the second for the replacement value.

[#5]

Hi thanks for your reply

the name of the field CustomerName is after the "Inner Join"

...Inner Join

SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as CustomerName,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordlevadrlandskod as CustomerCountry,ordrestnr

FROM JvsNL01.dbo.oh where ordstat<>'90'order by OrderNumber;

[Gysbert_Wassenaar]

The preceding load with your applymap only applies to the results of the first select statement. Since that doesn't contain the field CustomerName you get an error. Try adding a preceding load to the second select statement and use applymap there.

[#7]

many thanks to all as reply to my question! 🙂

[Agis-Kalogiannis]

I know that Gysbert. 🙂

The Alonte table that I mentioned was the parent table that contains the applymap() function, and not the mapping table.

The second parameter of the applymap() function, in this case Alsis, need to exist as a field on the same table, in this case, Alonte.