topic Re: Using Map-function (Applymap) in a sql statement in App Development

Using Map-function (Applymap) in a sql statement

magnusrydberg — Mon, 24 Apr 2017 12:30:59 GMT

Hi,

I'm trying to use "Applymap" in a SQL-statement but I don't manage to get it work. Maybe it isn't possible?

Code exemple:

MapItemnumbertoItemGroup:

MAPPING LOAD ItemNumber as CompanyItemNumber,

ItemGroup as CompanyItemGroup;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.CompanyItem;

LOAD LONumber,

Network,

OrderType,

LOType,

TransportCompany as Company,

TransportFacility as Facility;

SQL SELECT *

FROM "MyL_PROD_220".OTSDATA.LOHeader where Network='Interlink' and Status='90';

Left Join

LOAD

LONumber,

ApplyMap('MapItemnumbertoItemGroup', ItemNumber, null()) as MaterialItemGroup,

ReportedQty as MaterialReportedQty,

ItemNumber as MaterialItemNumber;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.LOMaterial where (ApplyMap('MapItemnumbertoItemGroup', ItemNumber, null())='Tid') ;

Is there any other alternative solution?

My original problem is really that I want to do a select of records from LOMaterial depending on a field (ItemGroup) in CompanyItem.

Thanks in advance!

// Magnus

Re: Using Map-function (Applymap) in a sql statement

marcus_sommer — Mon, 24 Apr 2017 13:53:50 GMT

Try this:

MapItemnumbertoItemGroup:

MAPPING LOAD ItemNumber as CompanyItemNumber,

ItemGroup as CompanyItemGroup;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.CompanyItem;

LOAD LONumber,

Network,

OrderType,

LOType,

TransportCompany as Company,

TransportFacility as Facility;

SQL SELECT *

FROM "MyL_PROD_220".OTSDATA.LOHeader where Network='Interlink' and Status='90';

Left Join

LOAD

LONumber,

ApplyMap('MapItemnumbertoItemGroup', ItemNumber, null()) as MaterialItemGroup,

ReportedQty as MaterialReportedQty,

ItemNumber as MaterialItemNumber

where (ApplyMap('MapItemnumbertoItemGroup', ItemNumber, null())='Tid') ;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.LOMaterial;

- Marcus

Re: Using Map-function (Applymap) in a sql statement

magnusrydberg — Mon, 24 Apr 2017 14:19:33 GMT

Thanks a lot Marcus it works fine

// Magnus

Re: Using Map-function (Applymap) in a sql statement

vinieme12 — Mon, 24 Apr 2017 14:20:01 GMT

ApplyMap() is native to Qlikview your database will only understand functions native to it for example

you cannot use Oracle db functions on a SQL Server database

Filter the rows in the where clause of the LOAD statement of Qlikview

Re: Using Map-function (Applymap) in a sql statement

magnusrydberg — Mon, 24 Apr 2017 14:23:59 GMT

Thanks a lot, It works fine

// Magnus