Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, please help me remove records that had no match after Applymap. In the example below, I need to remove records that return with 0 in B_IT field. Thank you in advance!
LOOKUP:
MAPPING
LOAD [Reporting Resp Cd],
Business_IT
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
Project:
LOAD
[Project Name],
[Reporting Resp Cd]
ApplyMap('LOOKUP',[Reporting Resp Cd],0) as B_IT
FROM
[Project.xlsx]
(ooxml, embedded labels, table is Sheet1)
You can try this also:
LOOKUP:
MAPPING
LOAD [Reporting Resp Cd],
Business_IT
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
Project:
LOAD [Project Name],
[Reporting Resp Cd]
ApplyMap('LOOKUP',[Reporting Resp Cd],0) as B_IT
FROM
[Project.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where ApplyMap('LOOKUP',[Reporting Resp Cd],0) <> 0;
You need to load into a temp table first, then load again excluding the unwanted rows using NoConcatenate . Finally drop the temp table.
LOOKUP:
MAPPING
LOAD [Reporting Resp Cd],
Business_IT
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
TmpProject:
LOAD
[Project Name],
[Reporting Resp Cd]
ApplyMap('LOOKUP',[Reporting Resp Cd],0) as B_IT
FROM
[Project.xlsx]
(ooxml, embedded labels, table is Sheet1)
Project:
NoConcatenate
LOAD
[Project Name],
[Reporting Resp Cd]
B_IT
Resident TmpProject
where B_IT <> '0';
drop table TmpProject;
You can try this also:
LOOKUP:
MAPPING
LOAD [Reporting Resp Cd],
Business_IT
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
Project:
LOAD [Project Name],
[Reporting Resp Cd]
ApplyMap('LOOKUP',[Reporting Resp Cd],0) as B_IT
FROM
[Project.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where ApplyMap('LOOKUP',[Reporting Resp Cd],0) <> 0;
Thank you both for quick response!
Hi,
without Applymap, you can try this also :
Project:
LOAD [Reporting Resp Cd],
Business_IT as B_IT
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
LEFT JOIN
LOAD
[Project Name],
[Reporting Resp Cd]
FROM
[Project.xlsx]
(ooxml, embedded labels, table is Sheet1);
with Applymap, you can try this also :
TEMP:
LOAD [Reporting Resp Cd]
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
LOOKUP:
MAPPING
LOAD [Reporting Resp Cd],
Business_IT
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Business_IT);
Project:
LOAD
[Project Name],
[Reporting Resp Cd],
ApplyMap('LOOKUP',[Reporting Resp Cd],0) as B_IT
FROM
[Project.xlsx]
(ooxml, embedded labels, table is Sheet1)
where Exists ([Reporting Resp Cd]);
DROP TABLE TEMP;