How to remove records that not match after Applymap?

[posywang]

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)

[Colin-Albert]

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;

[sunny_talwar · Accepted solution]

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;

[posywang]

Thank you both for quick response!

[#5]

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;