Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks a lot Marcus it works fine
// Magnus
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
Thanks a lot, It works fine
// Magnus