topic Re: Data model question - combining tables with more than one common field. - urgently required in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

luismadriz — Fri, 17 Nov 2017 03:40:29 GMT

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

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

Anonymous — Fri, 17 Nov 2017 03:51:08 GMT

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.

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

luismadriz — Fri, 17 Nov 2017 04:01:40 GMT

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?

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

Anil_Babu_Samineni — Fri, 17 Nov 2017 04:04:13 GMT

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;

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

luismadriz — Tue, 21 Nov 2017 07:13:28 GMT

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