topic Re: ApplyMap in QlikView

ApplyMap

Fri, 12 Jun 2015 11:43:49 GMT

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

Re: ApplyMap

Gysbert_Wassenaar — Fri, 12 Jun 2015 12:00:59 GMT

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

Re: ApplyMap

Agis-Kalogiannis — Fri, 12 Jun 2015 12:22:26 GMT

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

Re: ApplyMap

Gysbert_Wassenaar — Fri, 12 Jun 2015 12:28:27 GMT

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.

Re: ApplyMap

Fri, 12 Jun 2015 12:58:50 GMT

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;

Re: ApplyMap

Gysbert_Wassenaar — Fri, 12 Jun 2015 13:25:55 GMT

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.

Re: ApplyMap

Fri, 12 Jun 2015 13:35:42 GMT

many thanks to all as reply to my question! 🙂

Re: ApplyMap

Agis-Kalogiannis — Fri, 12 Jun 2015 14:16:19 GMT

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.