Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Link two fields from one table, to one field in the other

Hey, run into a small problem that I hope is easily solved.

So I've got one table that has TableID, AllocatedWorkerID, ReportedWorkerID.

The Allocated and Reported IDs are linked to the same table, which is Users, and are linked by UserID.

I need to be able to see what user reported the work, and what user has been allocated the work, so I can't just load them both "as UserID" or anything, so I'm a bit stumped at the moment.

Also the database is designed by someone else, and do not have the ability to change the architecture of it.

Thanks for any help.

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Link two fields from one table, to one field in the other

Hey Joseph,

If i understood correctly you want the Actual Names for AllocatedID and ReportedID fields from the User table using UserID field.

If this is the case you can use applymap() function. Something like this:

Mapping_Table:

Mapping

Load Distinct

UserID,

UserName

From User.qvd(qvd);

Actual_Table:

Load *,

Applymap('Mapping_Table',AllocatedID,'Missing :'&AllocatedID) as [Allocated Worker],

Applymap('Mapping_Table',ReportedID ,'Missing :'&ReportedID ) as [Reported Worker]

From Actual_Table.qvd(qvd);

Hope it helps

Thanks

AJ

4 Replies
chiru_thota
Not applicable

Re: Link two fields from one table, to one field in the other

You can load user Dimension table twice and rename columns.

Then your  problem will be solved.

Not applicable

Re: Link two fields from one table, to one field in the other

Hey Joseph,

If i understood correctly you want the Actual Names for AllocatedID and ReportedID fields from the User table using UserID field.

If this is the case you can use applymap() function. Something like this:

Mapping_Table:

Mapping

Load Distinct

UserID,

UserName

From User.qvd(qvd);

Actual_Table:

Load *,

Applymap('Mapping_Table',AllocatedID,'Missing :'&AllocatedID) as [Allocated Worker],

Applymap('Mapping_Table',ReportedID ,'Missing :'&ReportedID ) as [Reported Worker]

From Actual_Table.qvd(qvd);

Hope it helps

Thanks

AJ

oscarcanor
Not applicable

Re: Link two fields from one table, to one field in the other

CrossTable(TypeOfWork,UserID)

LOAD TableID,

     AllocatedWorkerID,

     ReportedWorkerID

Resident tabla1;

LOAD UserID

     Name

FROM Users;

Not applicable

Re: Link two fields from one table, to one field in the other

Thanks for answers everyone, but this is the one I tried first and it worked absolutely perfectly.

Thank you so much.