topic Re: Normalising mapping table in QlikView

Normalising mapping table

Anonymous — Tue, 06 Jun 2017 13:50:42 GMT

Hi I have the following mapping table 'SALES_MAPPING' which I am mapping to my main table SALES.. I want to normalise some of the SALES_PERSON values so the names appear as in the per the 'normalised' tab. ( Effectively I am just reducing them to single names, without surnames, initials etc ).

I am using a standard mapping load statement to load in the mapping table.

Where would I write the modifications to normalise the values - would it be in the mapping load statement or in the applymap statement and if so what should it look like ?

Thanks

Re: Normalising mapping table

sunny_talwar — Tue, 06 Jun 2017 13:55:18 GMT

So the above is your Mapping Table? You can do this

MappingTable:

Mapping

LOAD SALES_ID,

SubField(SALES_PERSON, '.', 1) as SALES_PERSON

FROM ....;

and then use ApplyMap as you would....

Re: Normalising mapping table

RonaldDoes — Tue, 06 Jun 2017 13:55:30 GMT

Hi Shibli,

Since Normalised also contains the SALES_ID, why don't you just map the Normalised table to your data?

Re: Normalising mapping table

prieper — Tue, 06 Jun 2017 13:57:41 GMT

mapNames:

LOAD Sales_ID, SALESPERSON FROM Sales_Mapping.xlsx (ooxml, embedded labels, table is [Normalized]);

Data:

LOAD

APPLYMAP('mapNames', Sales_ID) AS SALESPERSON_NORMALIZED

FROM .....;

Re: Normalising mapping table

Anonymous — Tue, 06 Jun 2017 14:01:33 GMT

Sorry should have clarified the normalised tab was my expected outputted values of the original mapping. For my real life example the mapping table may be updated so I need to script to pick up these changes.

Re: Normalising mapping table

prieper — Tue, 06 Jun 2017 14:08:03 GMT

SUBFIELD(SALESPERSON, '.', 1) AS FirstName

should do?