Mapping in order to replace multiple rows

[#1]

Hi I have the attached sample data. Effectively a main table with the associated lookup on the second tab to replace department and business unit matching on sales_id. As compared to a left join I want to keep the original department and business unit values from the main table. Is there an easy way to do this rather than write 3 separate mapping loads ?

[sunny_talwar · Accepted solution]

May be something like this?

MappingTable:

Mapping

LOAD Sales_ID,

    Department&'|'&[Business Unit]

FROM

[SAMPLE (1).xlsx]

(ooxml, embedded labels, table is Lookup);

Sales:

LOAD Sales_ID,

    Sales_amount,

    Sales_Person,

    Department,

    [Business Unit],

    SubField(ApplyMap('MappingTable', Sales_ID, Department&'|'), '|', 1) as [New Department],

    SubField(ApplyMap('MappingTable', Sales_ID, '|'&[Business Unit]), '|', 2) as [New Business Unit]

FROM

[SAMPLE (1).xlsx]

(ooxml, embedded labels, table is Sales);

[el_aprendiz111]

HI

[TABLE]:

LOAD * Inline

[

Sales_ID, Sales_amount, Sales_Person, Department, Business Unit

100, 1000, Adam, HR, North

101, 2000, Michael, FINANCE, South

102, 5000, Page, IT, East

103, 3000, Jim, COMMERCIAL, West

104, 2000, Sarah, FACILITIES, East

];

Left Join(TABLE)

LOAD * Inline

[

Sales_ID, Business Unit 2

101, INTERNATIONAL

102, NATIONAL

103, STATE

];

[#4]

Thanks Sunny. Great solution.