Problem with dimensions when creating a LinkTable ... - Qlik Community

dato6091 · ‎2021-12-08

Hi! I am doing the exercises of the book QlikView Your Business and I am having troubles with the dimensions that go to the LinkTable.

I have two fact tables Sales and CreditMemos, following the steps in the book I am creating two different keys %SalesKey and %CMKey both composed of the same fields %ProductID, %CustomerID, Date and %SalespersonID.

After that, I create the LinkTable using a DISTINCT LOAD with the following expression:

Repeating the above code for the data of the CreditMemo table and doing it again for the Sales table replacing the field %ProductID with the string '*ALL*' for the %CMKey (this is to connect all the fields from the CreditMemo table that don´t have the %ProductID description with the Sales table).

The code seems to work well, but when I try to visualize the result in a straight table, the dimensions that belong to the LinkTable don´t work as expected.

I have managed to solve it by creating the keys (%SalesKey and %CMKey) in both tables (Sales and CreditMemos) and, instead of creating them again in the LinkTable, just loading the fields. But I would like to understand why this happens.

I add the qvw app and the QVDs.

Thank you all in advance.

jwaligora · ‎2021-12-08

Hi.

In your attachment it looks like there are too many loads associated with the link table.
My best guess at what the book author is after is that there should only be two, and each one should have a line with "ALL" in it.

There are some qvd's that are missing from the zip, and also I don't have the book, so I can't validate.
Give it a shot and see how far off the mark this gets you.

dato6091 · ‎2021-12-10

Hi Jwaligora! Thanks for your reply.

The thing is that only in the CreditMemos table there are rows with no [% ProducID] associated, but table Sales has all the fields filled. To recover these lines I have used the following command to avoid NULL values:

NullAsValue [% Product ID];
SET NullValue = '*ALL*';

So, for the rows that don´t have [% ProducID] the keys in the CreditMemo table will have the string *ALL*. If I want to connect these rows with the key of the Sales table I have to generate two %CMKey for each one of the %SalesKey, as can be seen in the following image:

This is why there is a third concatenate with the LinkTable that comes from the Sales table, replacing [% Product ID] with the string '*ALL*' for the %CMKey.

I have updated the files, so now you can try it again if you want.

The solution of the book is the qvw that ends with 1 and my solution in the one that ends with 2. The main difference is that I am loading the %.. Key fields from the tables instead of forming them again in the LinkTable.

jwaligora · ‎2021-12-10

I see a %LinkKey field. Was that one part of the book's recommendation or did the book explicitly recommend using %CMKey and %SalesKey?

Also, do you have examples of front-end tables that would show what you expect to see as the final data relationships/behaviour?

Specifying '*ALL*' in the %CMKey shouldn't be necessary; keys with '*ALL*' in them should be created automatically with your NullAsValue clause, UNLESS the endgame is force an artificial relationship between Sales and CM to bring together records that would otherwise have no association.

...I did notice some weird behaviour where your keys end up with empty values in place of '*ALL*'. That appears to be tied to column names being reused during various stages of data processing, and can be addressed with a few strategically placed Qualify/Unqualify statements. I'm attaching the script with that adjustment, although as per the paragraph above, I'm not 100% sure this is what your're after.

dato6091 · ‎2021-12-13

Yes, the objective is to force a relationship between those two tables, otherwise products from the Sales table that don´t have the [% Product ID] field filled in the CreditMemos table can´t be categorized by any of the fields of this second table (CreditMemos).

Unfortunately I don´t have any front end table from the solution.

Thank you for your answer again.

Problem with dimensions when creating a LinkTable with a Distinct Load