Join issue

[#1]

Hi All,

I have one big question that confuse and drive me mad so much, I spent a lot of time on it. I really need your help. Thanks.

I have 4 tables:

Orders:

LocaleId,MemberId,OrderId,TimeId
1 ,1,1,1
1,2,2,1

Revenue:

LocaleId,MemberId,OrderId,TimeId,Amount
1 ,1,1,1,30

ChargeTimes:

LocaleId,OrderId,TimeId,ChargeTimes
1,1,1,1
1,2,1,0

Track:
LocaleId,TimeId,Visits
1,4,10
2,2, 10
3,1,10

You will find orders connects revenue with syn(LocaleId,MemberId,OrderId)

ChargeTimes uses syn(LocaleId,OrderId,TimeId)

Track uses syn(LocaleId,TimeId)

But after I load data into qvw, the results are not what I expected.

I use LocaleId as the selection: it has 1,2,3. three options.

But when I choose 1,2,3 and use a text to show:sum(Visits), it always gives me 0. But if I don't choose anything, Clear the selection, it will show the right num 30.

I don't know why, I really would like you to explain it and show me how do they join.

The test code is shown below, thanks.

Order:
load * Inline
[LocaleId,MemberId,OrderId,TimeId
1 ,1,1,1
1,2,2,1

];

Revenue:
load * Inline
[LocaleId,MemberId,OrderId,TimeId,Amount
1 ,1,1,1,30
];

ChargeTimes:
load * Inline
[LocaleId,OrderId,TimeId,ChargeTimes
1,1,1,1
1,2,1,0
];

Track:
load * Inline
[LocaleId,TimeId,Visits
1,4,10
2,2, 10
3,1,10
];

Many many thanks!!!

[Miguel_Angel_Baeyens · Accepted solution]

Hello Isaac,

Using the following will work, as the syn(LocaleId, OrderId, TimeId) is common for Orders, Revenue and ChargeTimes, but it's not in Track although data will be loaded. That syn key is shared for these three tables, but not in the last that uses only two of these three fields.

Check with

Order:load * Inline[LocaleId,MemberId,OrderId,TimeId1,1,1,11,2,2,1];Revenue:left join load * Inline[LocaleId,MemberId,OrderId,TimeId,Amount1,1,1,1,30];ChargeTimes:left join load * Inline[LocaleId,OrderId,TimeId,ChargeTimes1,1,1,11,2,1,0];Track:load * Inline[LocaleId,TimeId,Visits1,4,102,2,103,1,10];

Hope this helps

[#3]

Hi Miguel Angel Baeyens ,

Thanks for your reply, now I know the shared syn is (LocaleId, OrderId, TimeId).

But if I remove ChargeTimes table and don't use left join, then the results is also right.

I mean:

Order:
load * Inline
[LocaleId,MemberId,OrderId,TimeId
1 ,1,1,1
1,2,2,1

];

Revenue:
load * Inline
[LocaleId,MemberId,OrderId,TimeId,Amount
1 ,1,1,1,30
];

//ChargeTimes:
//load * Inline
//[LocaleId,OrderId,TimeId,ChargeTimes
//1,1,1,1
//1,2,1,0
//];

Track:
load * Inline
[LocaleId,TimeId,Visits
1,4,10
2,2, 10
3,1,10
];

Could please explain it again? Many thanks sir.

Isaac Li

[#4]

Hi Miguel Angel Baeyens,

Any updates? I am not pushing you, but it is very urgent for me. Thanks.

Isaac Li

[Miguel_Angel_Baeyens]

Synthetic keys are not joins, but all possible combinations of all fields involved in the synthetic key. Not loading that table will work because it's reducing the number of synthetic keys/tables produced, and the possible combinations return the results you want to see. That's not the desired behaviour when many tables share many fields from which you want to create composite keys.

Again, a synthetic key is produced between two tables when they share more than one field name, and is created for each record containing all possible combinations of all fields named alike. This is "stored" (quoted, I'm not a QlikView architect and don't know how are they actually stored in memory) in a synthetic table. For each synthetic key value in the left table you have a simmetric value in the right table (One record in Orders will have one record in Revenue). It's easier to see if you go to the Table Viewer and select in the above dropdown "Source Table View" instead of the default "Internal Table View". Loading only Order and Revenue tables you will see this way how they are connected by four fields instead of the $Syn table. Actually it's the same, just a different representation.

But it may happen that that some fields in the synthetic key (in your case LocaleId + MemberId + OrderId + TimeId) are part of another synthetic key (which has some of them but not all, in your case LocaleId + OrderId + TimeId, in the ChargeTimes table). Loading the previous plus ChargeTimes share three fields and one that is in two out of those three tables. Now you're highly likely not to get the correct values (since one field is missing in one table and there's no exact possible coincidence between them three, in other words, each key is unlikely to have a matching correspondence in each table).

Now load Orders, Revenue and Track. Check the table view in "Source Table View". Each table has, at least, two fields shared, which will likely return some desired results, because they three share at least three fields, so one record in Orders will likely have one match on Revenue which in turn is likely to have a matching record in Track.

Anyway, I would dare to say that in this case, it's more coincidence than desired results, and that you need more control over your data model than just leave QlikView create a handful of cartesian keys between all tables in the model.

Hope this makes a little sense.

[#6]

Hi,

Many thanks your long and detail reply, it helps me a lot. Thanks sir.

Now please allow me to get back to the code you provided.

Order:
load * Inline
[LocaleId,MemberId,OrderId,TimeId
1,1,1,1
1,2,2,1
];
Revenue:
left join load * Inline
[LocaleId,MemberId,OrderId,TimeId,Amount
1,1,1,1,30
];
ChargeTimes:
left join load * Inline
[LocaleId,OrderId,TimeId,ChargeTimes
1,1,1,1
1,2,1,0
];
Track:
load * Inline
[LocaleId,TimeId,Visits
1,4,10
2,2,10
3,1,10
];

After using left join, there exist two tables: Order and Track. But it also shares syn key (LocaleId, TImeId).

I know we can use link table to avoid this syn. So could give me some samples to avoid that syn key by link table?

Many thanks.

Isaac Li

[Miguel_Angel_Baeyens]

Hello Isaac,

I'm sorry but I don't see how a link table could be useful here. You may have get that information in another post?

If you want to avoid synthetic keys in your example, then create two new tables with the fields you need to for the keys, then drop the originals:

Order:load * Inline[LocaleId,MemberId,OrderId,TimeId1,1,1,11,2,2,1]; Revenue:left join load * Inline[LocaleId,MemberId,OrderId,TimeId,Amount1,1,1,1,30]; ChargeTimes:left join load * Inline[LocaleId,OrderId,TimeId,ChargeTimes1,1,1,11,2,1,0]; TableWithCompositeKey:LOAD LocaleId & '/' & TimeId AS CompositeKey, OrderId, MemberId, Amount, ChargeTimesRESIDENT Order; Track:load * Inline[LocaleId,TimeId,Visits1,4,102,2,103,1,10]; TrackWithCompositeKey:LOAD LocaleId & '/' & TimeId AS CompositeKey, VisitsRESIDENT Track; DROP TABLE Order;DROP TABLE Track;

Hope this helps.

[kaushalview]

Hi,

I attached one example

Regards

Kaushal Mehta

[#9]

Thanks all!

[kaushalview]

Hi,Issac

Please Verify my answer

Regrads

Kaushal mehta