topic Re: Applymap not working in QlikView

Applymap not working

Tue, 10 Feb 2015 15:47:21 GMT

Hey everyone,

Here is my code :

HR_EMPLOYEES:

mapping load

ATTRIBUTE5,

EMPLOYEE_ID

from HR_EMPLOYEES.qvd(qvd);

test:

LOAD

RESOURCENAME as ATTRIBUTE5,

Applymap('HR_EMPLOYEES', RESOURCENAME) as employee_id,

EVENTCODEID,

EVENTDATETIME as Event_shift_time

from OtherTable.qvd (qvd);

In the output of table "test", the "employee_id" field is supposed to contain the values of EMPLOYEE_ID (in HR_EMPLOYEES), but it doesn't, the field employee_id contains the same values as ATTRIBUTE5.

I really don't get why 😕

Any idea?

thank you for your help

Laura

Re: Applymap not working

Anonymous — Tue, 10 Feb 2015 15:54:29 GMT

please check if the values of ATTRIBUTE5 from HR_EMPLOYEES and RESOURCENAME from othertable

are identical. the behaviour described would result if no match is found. maybe different upper/lower case?

if number different number format??

Re: Applymap not working

jpapador — Tue, 10 Feb 2015 15:55:51 GMT

Since you didnt specify a default value in your apply map statement, it is just returning RESOURCENAME from the test table.

It doesnt look like it is finding any matches between ATTRIBUTE5 and RESOURCENAME. Are they in the same format?

Re: Applymap not working

rubenmarin — Tue, 10 Feb 2015 15:58:14 GMT

Hi Laura, ApplyMap() keeps the value of the ValueToSearch if this doesn't exist in the Map table, I would load the values of HR_EMPLOYEES in an isolated table (unique field names, ie adding MAP_ prefix to the fields name) and check if there is the same value for MAP_ATTRIBUTE5 and ATTRIBUTE5 (no blank spaces or anything different).

Re: Applymap not working

Tue, 10 Feb 2015 16:02:49 GMT

Hi Laura,

The technique used for ApplyMap seems to be correct.

Just check the values in 'ATTRIBUTE5' matches with 'RESOURCENAME'.

Also, give the thhird parameter of the ApplyMap with a default vale.

Hope it helps!

Cheers !

Re: Applymap not working

NickHoff — Tue, 10 Feb 2015 16:03:30 GMT

Try the statement below and if you are getting Invalid for each field the match isn't being met. If that is the case can you provide a table box of two fields to see why the match isn't being made?

HR_EMPLOYEES:

MAPPING LOAD

EMPLOYEE_ID,

ATTRIBUTE5

FROM HR_EMPLOYEES.qvd(qvd);

TEST:

LOAD

RESOURCENAME as ATTRIBUTE5,

APPLYMAP('HR_EMPLOYEES', RESOURCENAME,'Invalid') AS EMPLOYEE_ID,

EVENTCODEID,

EVENTDATETIME as Event_shift_time

from OtherTable.qvd (qvd);

Re: Applymap not working

Peter_Cammaert — Tue, 10 Feb 2015 16:04:50 GMT

Or temprarily remove the MAPPING prefix from your code. You'll get an additional table that uses ATTRIBUTE5 as a key to connects EMPLOYEE_ID to the test table.

Use a Table box to check wether every EVENTCODEID has an ATTRIBUTE5 value and a corresponding EMPLOYEE_ID.

Re: Applymap not working

Tue, 10 Feb 2015 16:10:50 GMT

you are right, I've put a default value in the applymap and it always returns this value.

That's very weird because I 've tried :

HR_EMPLOYEES:

load

ATTRIBUTE5,

EMPLOYEE_ID

from HR_EMPLOYEES.qvd(qvd);

test:

LOAD

RESOURCENAME as ATTRIBUTE5,

EVENTCODEID,

EVENTDATETIME as Event_shift_time

from OtherTable.qvd (qvd);

So I do get 2 tables linked on ATTRIBUTE5 and then, I do have an EMPLOYEE_ID for each ATTRIBUTE5...

I use an old version of Qlikview (9), might this be the reason ?

Thank you again

Re: Applymap not working

jpapador — Tue, 10 Feb 2015 16:15:16 GMT

Is EMPLOYEE_ID and ATTRIBUTE5 a 1:1 relationship. Maps only work correctly for a 1:1 relationship while just creating a link between the tables would work for 1:n relationships.

You could try doing a left join instead of a map and see if that produces values:

test:

LOAD

RESOURCENAME as ATTRIBUTE5,

EVENTCODEID,

EVENTDATETIME as Event_shift_time

from OtherTable.qvd (qvd);

Left Join (test)

load

ATTRIBUTE5,

EMPLOYEE_ID as newemployeeid

from HR_EMPLOYEES.qvd(qvd);

Re: Applymap not working

Tue, 10 Feb 2015 16:20:26 GMT

Thank you !

I've changed my code to :

HR_EMPLOYEES:

Mapping load distinct

ATTRIBUTE5,

EMPLOYEE_ID

from HR_EMPLOYEES.qvd(qvd)

where not isnull(ATTRIBUTE5) ;

And it is now working !

Re: Applymap not working

rubenmarin — Tue, 10 Feb 2015 16:20:30 GMT

Hi Laura, with this last test you've done you can try to create a table box with EMPLOYEE_ID and EVENTCODEID, if both values are in the same row, then a linked value exist, otherwise HR_EMPLOYEES.ATTRIBUTE5 is different than test.ATTRIBUTE5.

If can upload a sample we can get you a direct solution instead of guiding tests.

Re: Applymap not working

Tue, 10 Feb 2015 16:32:42 GMT

Ok, I have resolved my first trouble, now I have another applymap which is not working :

Code_event_tl:

mapping LOAD

EVENTCODEID,

EVENTCODENAME

from EVENT_CODE.qvd (qvd)

;

test:

LOAD

RESOURCENAME as ATTRIBUTE5,

Applymap('HR_EMPLOYEES', RESOURCENAME, 'null') as EMPLOYEE_ID,

Applymap('Code_event_tl',EVENTCODEID, 'null') as Event_shift,

EVENTCODEID,

EVENTDATETIME as Event_shift_time

from OtherTable.qvd (qvd);

This is the "Event_shift" value which always returns null... whereas the Code_event_tl table is a simple table with 7 rows.

I also notice that when I look at the "schema view" ( where you can see the tables and the relationships), there is a lonely table called "$orphan_EVENTCODENAME" and it contains EVENTCODENAME.

What is that ?

Thanks again

Laura

Re: Applymap not working

rubenmarin — Tue, 10 Feb 2015 16:45:25 GMT

I never saw an $orphan table, so I have no idea, anyway, have you tried to use the Distinct clause like in the last map?

Re: Applymap not working

Tue, 10 Feb 2015 16:48:24 GMT

Mapping $Orphan

I might have found the problem