topic Re: Mapping in order to replace multiple rows in QlikView

Mapping in order to replace multiple rows

Fri, 07 Oct 2016 13:58:18 GMT

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 ?

Re: Mapping in order to replace multiple rows

sunny_talwar — Fri, 07 Oct 2016 14:04:45 GMT

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);

Re: Mapping in order to replace multiple rows

el_aprendiz111 — Fri, 07 Oct 2016 14:13:52 GMT

[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

];

Re: Mapping in order to replace multiple rows

Fri, 07 Oct 2016 17:17:01 GMT

Thanks Sunny. Great solution.