topic Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ? in QlikView

Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

paulyeo11 — Tue, 17 Jan 2017 19:03:36 GMT

Hi All

Below code given by Henric Cronström , working fine , able to make use of only 1 script below loading 2 set of raw data Singapore and Malaysia :-

For each vFileName in FileList('$(vRAWPath)\*.txt')

ARSummary:

LOAD left(FileBaseName(), 4) AS Report5_pay,

FileBaseName() as SOURCE,

@1:9T as [cust_id],

@10:38T as [company_pay],

ApplyMap('MapGroup',GROUP_CLASS,'OTHERS') as [GROUP_CLASS_]

FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);

Next vFileName

Now the above ApplyMap only can recode :-

if load Singapore or Malaysia raw data G1 it will recode G1-SG

I like to know for the above Mapping recode , does it allow add condition ?

if load Singapore raw data G1 it will recode to G1-SG

if load Malaysia raw data G1 it will recode G1-MY

Can some one tell me it is possible ?

Paul

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

Digvijay_Singh — Tue, 17 Jan 2017 19:50:49 GMT

You may create two map files based on country and use 'if statement' in the script to decide which map to use. But you need to have source field to identify the country in your if statement.

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

paulyeo11 — Tue, 17 Jan 2017 20:10:09 GMT

Hi Sir

Thank you very much for your advise.

I have follow your advise. if i create 2 seperate line of script , it work fine :-

ApplyMap('MapGroup_SG',GROUP_CLASS,'OTHERS') as [GROUP_CLASS_1],

ApplyMap('MapGroup_MY',GROUP_CLASS,'OTHERS') as [GROUP_CLASS_2]

When i try to use the if command below , i get error :-

if (SOURCE='Q_DOD_2016','ApplyMap('MapGroup_SG',GROUP_CLASS,'OTHERS')',

if (SOURCE='Q_FCKL09','ApplyMap('MapGroup_MY',GROUP_CLASS,'OTHERS')',

)) as [GROUP_CLASS_3],

Can you advise me where go wrong ?

Enclosed my simple file for testing , kindly unzip the file into c:\RECODE\

Paul

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

vinieme12 — Tue, 17 Jan 2017 20:17:42 GMT

Try below without quotes for apply map

if (SOURCE='Q_DOD_2016',ApplyMap('MapGroup_SG',GROUP_CLASS,'OTHERS'),

if (SOURCE='Q_FCKL09',ApplyMap('MapGroup_MY',GROUP_CLASS,'OTHERS'),

)) as [GROUP_CLASS_3],

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

Digvijay_Singh — Tue, 17 Jan 2017 20:21:22 GMT

I don't have license to open qvw but you can simplify the statement if you have only two countries as below -

if (SOURCE='Q_DOD_2016',

ApplyMap('MapGroup_SG',GROUP_CLASS,'OTHERS'),

ApplyMap('MapGroup_MY',GROUP_CLASS,'OTHERS'),

)) as [GROUP_CLASS_3],

You may try removing apostrophe before applymap else it would consider it as string.

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

paulyeo11 — Tue, 17 Jan 2017 20:28:58 GMT

Hi Sir

I just try using both your expression , i get no error. But then it recode 1st row not correct , second row correct , only all even row correct.

Paul

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

vinieme12 — Tue, 17 Jan 2017 20:40:17 GMT

Check your Mapping table for MapGroup_MY, it may have values for your SG companies

Re: Henric Cronström load multiple raw data using 1 script , ApplyMap how to add ?

paulyeo11 — Tue, 17 Jan 2017 21:03:45 GMT

Hi Sir

sales_G:

LOAD GROUP_CLASS,grouping,

if (SOURCE='Q_DOD_2016',ApplyMap('MapGrouping_SG',GROUP_CLASS,'OTHERS'),

if (SOURCE='Q_FCKL09',ApplyMap('MapGrouping_MY',GROUP_CLASS,'OTHERS'),

)) as [GROUP_CLASS_3],

ApplyMap('MapGroup_SG',GROUP_CLASS,'OTHERS') as [GROUP_CLASS_SG],

ApplyMap('MapGroup_MY',GROUP_CLASS,'OTHERS') as [GROUP_CLASS_MY]

resident sales;

When I try to create above script to get 3 field ,

GROIUP_CLASS3 Not working, GROUP_CALSS_SG & GROUP_CLASS_MY working fine, see the below image , where else go wrong ? can you suggest ?

If i use the GROUP_CLASS_SG and GROUP_CLASS_MY , GROUP_CLASS_9 it will work fine , but i will get error msg all field name must be unique,.