topic Re: Learn Apply Map in QlikView

Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 18:32:31 GMT

Hi All

I try to learn how to apply mapping .

May i know why cannot add MAPPING in below script ?

MAP:
//MAPPING
LOAD
SEG_RAW,
SEGMENT
FROM
C:\Users\pauly\Dropbox\mainqv\QV_PROJECT\MAPPING\MAPPAY.xlsx
(ooxml, embedded labels, table is ADL);

PAY:
LOAD
UserField1,
ApplyMap('Map_PAY',[UserField1],'OTHERS') as [SEGMENT_]
FROM
C:\Users\pauly\Dropbox\mainqv\QV_PROJECT\MAPPING\PAY_ADL.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Paul

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 18:42:04 GMT

Hi Paul,

You must define the table as a mapping table if you want to use the Apply map function.

If you do not define a mapping table, your Apply map function won't work.

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:00:50 GMT

Hi Kau

Thank you for your sharing. Can you share how to define the table as Mapping table ?

Paul Yeo

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:05:51 GMT

Hi,

All you have to do is add the "mapping" keyword before the load statement.

Now two things to remember about the mapping table.

1. It should have exactly 2 columns.

2. The first column should be the column for which you want to change the new value.

and the second column should represent your new value.

In your case the mapping table script will be as below.

MAP:
MAPPING
LOAD
SEG_RAW,
SEGMENT
FROM
C:\Users\pauly\Dropbox\mainqv\QV_PROJECT\MAPPING\MAPPAY.xlsx
(ooxml, embedded labels, table is ADL);

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:17:37 GMT

Hi Sir

I have define a mapping table , and also link the mapping table field i need to map UserField1 with my load script , still not working.

Map_Pay:
MAPPING
LOAD
UserField1,
SEGMENT
FROM
C:MAPPAY.xlsx
(ooxml, embedded labels, table is ADL);

PAY:
LOAD
UserField1,
ApplyMap('Map_PAY',[UserField1],'OTHERS') as [SEGMENT_]
FROM
C:PAY_ADL.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Paul

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:20:29 GMT

I don't see any error in the script.

What is the error that you get?

Can you share the data to recreate your issue on my environment?

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:23:30 GMT

Hi Sir

I don't get any error msg. just that it cannot recode .

enclosed my raw data.

Paul

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:30:36 GMT

Hi,

It didnt work because the Mapping table name that you were supplying in Applymap Function was wrong.

It should have been "Map_Pay" but you have used "Map_PAY".

Thus it didnt find the Mapping table and thus it didnt work.

Try with the exact mapping table name and it should work.

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:35:26 GMT

Hi Sir

I hv try to make both Map_Pay the same , but it still cannot work.

Any other suggestion ?

Paul

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:38:42 GMT

Hi Sir

I just test when i comment Mapping it work fine.

where go wrong ?

Paul

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:40:29 GMT

Hi,

It is working for me. See the attached file.

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:42:19 GMT

When you remove it, it is just like a normal association.

If you want to try, do this. Change the field name in Pay Table from UserField1 to UserField2.

When you do this, it wont show you the proper result.

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:47:11 GMT

Hi Sir

when i download the QVW file you send me , it work fine now.

Now when i try to comment Mapping , it does not work .

But if i try my own script , it still not working.

Paul

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:49:07 GMT

That should not happen. What version you are having?

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:50:09 GMT

Ver 9

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:51:24 GMT

What..?

Qlikview version 9?

Are you sure?

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:53:12 GMT

Yes i have been using this ver 9 and i never have any issue in the past , only recently i enclosed this issue.

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 19:55:38 GMT

Hi Sir

when you press control T , do you see only one table with 2 field ?

Paul

Re: Learn Apply Map

kaushiknsolanki — Sat, 18 Apr 2020 19:57:32 GMT

Thats true,

because the mapping table stays in RAM till script execution is going on. Once script is finished the Mapping table is flushed out of RAM.

Re: Learn Apply Map

paulyeo11 — Sat, 18 Apr 2020 20:06:50 GMT

Hi Sir

If i have 5 different company segment table.

should i using one mapping table for 5 company ?

because i need each company have it own recode.

Paul