ApplyMap question

[#1]

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?

[devarasu07]

Try like below,

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

[#3]

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.

[sunny_talwar]

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

[maxgro]

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);

[devarasu07]

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

[Digvijay_Singh]

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

[#8]

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.