topic Re: ApplyMap question in QlikView

ApplyMap question

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to use the ApplyMap function. It is working partially. It gets the first row correctly but any additional rows are neglected.

MapFile:

Mapping LOAD *
FROM
[New Event Names.xlsx]
(ooxml, embedded labels, table is Sheet1);

FullFile:

Load [Event Name],
ApplyMap('MapFile',[Event Name]) as NEWNAME
FROM
[S&C All Fields.xlsx]
(ooxml, embedded labels, table is [S&C All Fields]);

row from mapping file...

Old Name	New Name
Admin Login Successful	UA.U.DE

result...

Event Name	New Name
Admin Login Successful	UA.U.DE
Admin Login Successful	Admin Login Successful
Admin Login Successful	Admin Login Successful

Any ideas why this is happening?

Re: ApplyMap question

devarasu07 — Wed, 10 May 2017 17:19:18 GMT

Try like below,

ApplyMap('MapFile',[Event Name],'Undefined') as NEWNAME

Re: ApplyMap question

Wed, 10 May 2017 17:22:59 GMT

So now the 2nd and 3rd rows show 'undefined' instead of the original event name. Still not mapping correctly like the 1st row to UA.U.DE.

Re: ApplyMap question

sunny_talwar — Wed, 10 May 2017 17:23:13 GMT

Would you be able to share your Excel files to test?

Re: ApplyMap question

maxgro — Wed, 10 May 2017 17:27:58 GMT

Your script seems to work

MapFile:

Mapping

LOAD [Old Name],

[New Name]

FROM

[https://community.qlik.com/thread/260022]

(html, codepage is 1252, embedded labels, table is @1);

FullFile:

LOAD [Event Name],

ApplyMap('MapFile',[Event Name]) as [New Name]

FROM

[https://community.qlik.com/thread/260022]

(html, codepage is 1252, embedded labels, table is @2);

Re: ApplyMap question

devarasu07 — Wed, 10 May 2017 17:33:53 GMT

Hi,

Some Hint about mapping table & why we use it:

1) Mapping table must have two columns

2) first field should be key field (mapping key)

3) 2nd field will mapping value

Syntax: Applymap('mapping table name',match_keyfield,'match not found') as newCol

Why we use it?

->it's used to reduce the number of table

->it's like excel vlookup feature

->used to create star schema

->after script execution this mapping table will get auto dropped from memory

Re: ApplyMap question

Digvijay_Singh — Wed, 10 May 2017 17:35:36 GMT

May be spaces around event name, you may try trimming them before loading using trim()

Re: ApplyMap question

Wed, 10 May 2017 17:54:42 GMT

I think it is working after all.

When I was looking at the results in Qlikview is looked like 3 rows were 'Admin Login Successful'. So I expected all 3 to map to 'UA.U.DE'. In reviewing my source data though, only the first one is 'Admin Login Successful'. The other 2 rows were 'Admin Login Successful and User Login Success'. So those 2 rows shouldn't have mapped the way I have the logic.

Now I just have to figure out why those 2 rows are appearing without 'and User Login Success' in Qlikview.

Thanks all.