Data model question - combining tables with more than one common field. - urgently required

[Anonymous]

I have two tables.

First table has project number, investment number and business case.

Second table also has project number, investment number and business case.

In this scenario how to I create a link between the two tables . All other columns are separate.

If I have to create a data model by giving a logical link between the two tables what will be the script.

[luismadriz]

Hi,

Like a composite key with Autonumber([project number]&[investment number]&[business case]) as Key?

It'll be interesting to know what others have done,

Cheers,

Luis

[Anonymous]

Hi Luis, q1) Is it fine to use just concatenate instead of auto number.

Q2And if the concatenated key from one table does not match the other table it gives out blank rows, I have noticed that.

[luismadriz]

Hi,

Q1, yes, for small datasets

Q2, of course, if something doesn't exist on one table it doesn't have anything to join on. Example:

T1:

Load *

Inline [

Dim,NumberT1

1,503262

2,800202

3,707784

4,498978

5,603963

6,518502

7,432352];

T2:

Load *

Inline [

Dim,NumberT2

1,262

2,202

3,784

8,502

9,352];

Do the nulls worry you?

[Anil_Babu_Samineni]

Can we know the reason to maintain Bridge table?

Anyway, FYQ answer should be this?

T1:

Load *, AutoNumber([project number] & [investment number] & [business case]) as Key;

Load [project number], [investment number], [business case], Field_Diff1 From T1;

T2:

Load *, AutoNumber([project number] & [investment number] & [business case]) as Key;

Load [project number], [investment number], [business case], Field_Diff2 From T2;

LinkTable:

Load DISTINCT Key, [project number], [investment number], [business case] Resident T1;

Concatenate (LinkTable)

Load DISTINCT Key, [project number], [investment number], [business case] Resident T2;

Drop Fields [project number], [investment number], [business case] From T1;

Drop Fields [project number], [investment number], [business case] From T2;

[luismadriz]

Hi,

Just wondering to know how did you go,

Cheers

Luis

When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others