topic Re: Two fields calling another table in QlikView

Two fields calling another table

Tue, 14 Apr 2015 13:12:29 GMT

Hi,

I have a question.

I have a table "Employes" and a table "Datas".

Like this :

Employes :

KeyUser	Name
080503	Community Qlik !
080504	Smiley cool
080505	No records

Datas :

Date	KeyUserCreate	KeyUserModify
13/04/2015	080503	080504
14/04/2015	080503	080503
14/04/2015	080504	080503

I would like this result

Date	KeyUserCreate	NameCreate	KeyUserModify	NameModify
13/04/2015	080503	Community Qlik !	080504	Smiley cool
14/04/2015	080503	Community Qlik !	080503	Community Qlik !
14/04/2015	080504	Smiley cool	080503	Community Qlik !

I've tried to create two tables Employes but it doesn't works.

Could you help me?

Thanx

Re: Two fields calling another table

alexandros17 — Tue, 14 Apr 2015 13:16:59 GMT

Try this:

Load Date, KeyUserCreate, KeyUserModify From Datas;

Left Join

Load KeyUser as KeyUserCreate, Name as NameCreate From Employes;

Let me know

Re: Two fields calling another table

Tue, 14 Apr 2015 13:21:37 GMT

Thanx, but with this code, i have no value for NameModify.

Re: Two fields calling another table

Anonymous — Tue, 14 Apr 2015 13:24:09 GMT

See attached. The first table is used a s a map

Re: Two fields calling another table

fvelascog72 — Tue, 14 Apr 2015 13:24:26 GMT

Try this:

Employe:

Mapping LOAD

KeyUser,

Name

INLINE [

KeyUser, Name

080503, Community Qlik !

080504, Smiley cool

080505, No records

];

Datas:

LOAD

ApplyMap('Employe', KeyUserCreate, null()) as "NameCreate",

ApplyMap('Employe', KeyUserModify, null()) as "NameModify"

INLINE [

Date, KeyUserCreate, KeyUserModify

13/04/2015, 080503, 080504

14/04/2015, 080503, 080503

14/04/2015, 080504, 080503

];

Re: Two fields calling another table

Tue, 14 Apr 2015 13:40:17 GMT

I have an error.

Message is : Field KeyUserCreate not found.

The field KeyUserCreate doesn't exist in mapping table Employe.

Re: Two fields calling another table

buzzy996 — Tue, 14 Apr 2015 13:44:42 GMT

use this,

Employes:

Mapping Load * Inline [

KeyUser, Name

080503, Community Qlik !

080504, Smiley cool

080505 ,No records

];

Datas:

Load * Inline [

Date ,KeyUserCreate, KeyUserModify

13/04/2015, 080503, 080504

14/04/2015, 080503, 080503

14/04/2015, 080504,080503

];

Datas2:

Load

Date,

KeyUserCreate,

KeyUserModify,

ApplyMap('Employes',KeyUserCreate) as NameCreate,

ApplyMap('Employes',KeyUserModify) as NameModify

resident Datas;

drop table Datas;

or pfa

Re: Two fields calling another table

Tue, 14 Apr 2015 13:52:46 GMT

Thanx,

It's working.

Now, how can i use mapping with 3 fields?

Re: Two fields calling another table

buzzy996 — Tue, 14 Apr 2015 13:59:23 GMT

u mean u want to add another field?

Re: Two fields calling another table

alexandros17 — Tue, 14 Apr 2015 14:00:29 GMT

I did not notice the structure you needed, try this

Employes:
LOAD * Inline [
KeyUser, Name
080503, Community Qlik !
080504, Smiley cool
080505, No records
];

Datas:
LOAD * Inline [
Date, KeyUserCreate, KeyUserModify
13/04/2015, 080503, 080504
14/04/2015, 080503, 080503
14/04/2015, 080504, 080503
];

Final:
NoConcatenate
LOAD Date, KeyUserCreate, KeyUserModify Resident Datas;
Left Join
LOAD KeyUser as KeyUserCreate, Name as NameCreate Resident Employes;
Left Join
LOAD KeyUser as KeyUserModify, Name as NameModify Resident Employes;

DROP Tables Datas, Employes;

If you need other field use this logic:

Left Join
LOAD KeyUser as xxxx, Name as yyyyResident zzzz;

Re: Two fields calling another table

fvelascog72 — Tue, 14 Apr 2015 14:11:59 GMT

The file attached with the same script.