topic Re: How to recode 15 brand to 4 brand using Map thru Excel file ? in QlikView

How to recode 15 brand to 4 brand using Map thru Excel file ?

paulyeo11 — Sat, 23 May 2015 13:59:43 GMT

Hi All

I have below script , it does not give me any error :-

MapBrand:

Mapping LOAD

[Brand_],

[SubBrand_]

FROM mapsubbrand.xlsx (ooxml,embedded labels, table is [Sheet4]);

My excel file have below brand :-

Brand_ SubBrand_

HAKKO HAKKO

BECKHOFF BECKHOFF

MITSUBISHI MITSUBISHI

MITSUBISH MITSUBISHI

GE FANUC GE FANUC

GE GE FANUC

My script when load raw data :-

Applymap('MapBrand',(subfield(@282:298,' ',1)),@282:298) as [BRAND_],

@282:298T as [bRAND_], // Pls note this field have more then 10 brand. I need to display only 4 brand.

Now my issue is it display all the brand , I need you recode this filed BRAND_ only display 4 core brand , meaning i need it only display Beckhoff , Hakko , GE , Mitsubishi and Other ,Now it display all brand is wrong.

Hope some one can advise me.

Paul

Re: How to recode 15 brand to 4 brand using Map thru Excel file ?

paulyeo11 — Sat, 23 May 2015 14:02:10 GMT

Enclosed My QV doc

Re: How to recode 15 brand to 4 brand using Map thru Excel file ?

Clever_Anjos — Sat, 23 May 2015 14:19:46 GMT

Try

Applymap('MapBrand',(subfield(@282:298,' ',1)),'Others') as [BRAND_],

Re: How to recode 15 brand to 4 brand using Map thru Excel file ?

maxgro — Sat, 23 May 2015 15:46:32 GMT

//Applymap('MapBrand',(subfield(@282:298,' ',1)),@282:298) as [BRAND_],

Applymap('MapBrand',@282:298,'Other') as [BRAND_],

Re: How to recode 15 brand to 4 brand using Map thru Excel file ?

MarcoWedel — Sat, 23 May 2015 21:01:39 GMT

Hi,

not an answer to your question, but if you replace

@282:298

with

[@282:298]

then the editor will not show syntax error marks.

hope this helps

regards

Marco

Re: How to recode 15 brand to 4 brand using Map thru Excel file ?

paulyeo11 — Sat, 23 May 2015 23:11:54 GMT

Hi All

Thank you very much it work fine.

Paul