Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

magnusrydberg
New Contributor II

Using Map-function (Applymap) in a sql statement

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

1 Solution

Accepted Solutions

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

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

4 Replies

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

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

magnusrydberg
New Contributor II

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

Thanks a lot Marcus it works fine

// Magnus

vinieme12
Esteemed Contributor II

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

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

magnusrydberg
New Contributor II

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

Thanks a lot, It works fine

// Magnus

Community Browser