Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mapratt82
Contributor

Linking multiple fields in one table to single field in another?

So I'm needing to link a table that has "Primary", "Secondary", and "Tertiary" users back to the "UserID" in the User table. How can I accomplish this? Thanks in advance!

10 Replies

Re: Linking multiple fields in one table to single field in another?

Can you provide an example using a sample?

rupamjyotidas
Valued Contributor

Re: Linking multiple fields in one table to single field in another?

Do you mean to say no USER ID in the table which has "Primary", "Secondary", and "Tertiary",

Are "Primary", "Secondary", and "Tertiary" different fields or same fields like USERStatus?

mapratt82
Contributor

Re: Linking multiple fields in one table to single field in another?

I have Primary_UserID, Secondary_UserID, and Tertiary_UserID in the same table. I have User table that all their information keyed by UserID.

So in need:

Primary_UserID = UserID

Secondary_UserID = UserID

Tertiary_UserID = UserID

Hope that helps!

rupamjyotidas
Valued Contributor

Re: Linking multiple fields in one table to single field in another?

A datasample would be helpful

rupamjyotidas
Valued Contributor

Re: Linking multiple fields in one table to single field in another?

Something like this might help

Load

PrimayUSERID as UserID

*

'Primary' as Flag

From XYR

Concatenate

Load

SecondaryUSERID as UserID

*

'Secondary' as Flag

From XYR

Concatenate

Load

TertiaryUSERID as UserID

*

'Tertiary' as Flag

From XYR

shraddha_g
Honored Contributor III

Re: Linking multiple fields in one table to single field in another?

Provide the sample data and desired output

mapratt82
Contributor

Re: Linking multiple fields in one table to single field in another?

Unfortunately I'm not able to really do a data sample due to work at a bank and confidentiality.

Basically it would be similar to:

AcctTable

Acct = 12345

Primary_UserID = 00000

Secondary_UserID = 11111

Tertiary_UserID = 22222

Acct = 54321

Primary_UserID = 33333

Secondary_UserID = 22222

Tertiary_UserID = 44444

UserTable

UserID = 00000

Name = John Doe

UserID = 11111

Name = Jane Doe

UserID = 22222

Name = John Smith

UserID = 33333

Name = Jane Smith

UserID = 44444

Name = Batman

So you would have something like:

Name:                    Acct:

John Doe               12345

Jane Doe               12345

John Smith            12345

John Smith            54321

Jane Smith            54321

Batman                 54321

Thanks

ogster1974
Honored Contributor II

Re: Linking multiple fields in one table to single field in another?

Do something like this.

AccountUser:

LOAD

Acct

Primary_UserID AS "UserID"

'Primary' AS "UserType"

FROM Source Data WHERE Primary_UserID IS NOT NULL

LOAD

Acct

Secondary_UserID AS "UserID"

'Secondary' AS "UserType"

FROM Source Data WHERE Secondary_UserID IS NOT NULL

LOAD

Acct

Tertiary_UserID AS "UserID"

'Tertiary' AS "UserType"

FROM Source Data WHERE Tertiary_UserID IS NOT NULL

You will then be able to filter on types of users as a bonus.

Hope this helps

Andy

abhimalik
New Contributor III

Re: Linking multiple fields in one table to single field in another?

Hey Andy,

Can we do the same with this?

    

Vendor IDVendor NameDiverseLookup Type
AbcUSVendor Name
defUSVendor Name
qwwUSVendor Name
ertUSVendor Name
tyuUSVendor Name
yuiUSVendor Name
iopUSVendor Name
rgfUSVendor Name
ujyUSVendor Name
mjhUSVendor Name
xcvUSVendor Name
iogUSVendor Name
60054HFHGBUK Vendor Id
605391EsurtUKVendor Id
605719Ecoce UKVendor Id
600559mitedUKVendor Id
6056253Sing LtdUKVendor Id
605484litdUKVendor Id
605673affgUKVendor Id
645581ATABLEUKVendor Id
700105A.ScitorsUKVendor Id

I want this file to be linked with data model like where there is a vendor ID then all the records with Vendor Id should be picked and where there is no Vendor Id then records should be picked with Vendor Name.

Corresponding fields in my data model are:

vendor Id->Global Supplier

Vendor Name-> Supplier Name