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

[mapratt82]

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!

[sunny_talwar]

Can you provide an example using a sample?

[rupamjyotidas]

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]

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]

A datasample would be helpful

[rupamjyotidas]

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]

Provide the sample data and desired output

[mapratt82]

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]

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

[Anonymous]

Hey Andy,

Can we do the same with this?

Vendor ID	Vendor Name	Diverse	Lookup Type
	Abc	US	Vendor Name
	def	US	Vendor Name
	qww	US	Vendor Name
	ert	US	Vendor Name
	tyu	US	Vendor Name
	yui	US	Vendor Name
	iop	US	Vendor Name
	rgf	US	Vendor Name
	ujy	US	Vendor Name
	mjh	US	Vendor Name
	xcv	US	Vendor Name
	iog	US	Vendor Name
60054	HFHGB	UK	Vendor Id
605391	Esurt	UK	Vendor Id
605719	Ecoce	UK	Vendor Id
600559	mited	UK	Vendor Id
605625	3Sing Ltd	UK	Vendor Id
60548	4litd	UK	Vendor Id
605673	affg	UK	Vendor Id
645581	ATABLE	UK	Vendor Id
700105	A.Scitors	UK	Vendor 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