Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted

Can you provide an example using a sample?

Highlighted
Specialist
Specialist

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?

Highlighted
Creator
Creator

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!

Highlighted
Specialist
Specialist

A datasample would be helpful

Highlighted
Specialist
Specialist

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

Highlighted
Partner
Partner

Provide the sample data and desired output

Highlighted
Creator
Creator

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

Highlighted
Master II
Master II

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

Highlighted
Contributor III
Contributor III

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