Using Map-function (Applymap) in a sql statement

[magnusrydberg]

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

[marcus_sommer · Accepted solution]

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]

Thanks a lot Marcus it works fine

// Magnus

[vinieme12]

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]

Thanks a lot, It works fine

// Magnus