joining tables by more columns

[ZuzJsk007]

H all, let's imagine following situation:

I would like to select a value from field "node_ID" which would cause recalculation of other visualisations on the sheet associated to Orders via Order_ID. The problem is that with each selection of "node_ID", I want to select a different part of the Order data, but the selections may be overlapping in the data.

Example:

nodes:

node_ID	S1	S2	S3	S4
1	1
2		1
3			1
4				1

Orders:

Order_ID	S1	S2	S3	S4
1	1
2	1	1
3	1		1
4	1	1		1

where S1 to S4 are representations of selection I want to make, i.e.:

When selecting node_id = 2, then Order_ID = 2 and 4 are selected (visualisations are adjusted by this selection)

The question is: how to connect there two tables so that the associative model remains in tact?

[ArnadoSandoval · Accepted solution]

Hi @ZuzJsk007 

If I understood your problem, you need to implement a Link Table between your Nodes and Orders tables; (Odd enough, I had a job interview asking me about this Data Model structure, which I was unable to answer because you don't need them in most cases); The Link Table assist us joining the Nodes and Orders based on the Groups selections you described; The data model with a Link Table looks like the screenshot below:

The tables are:

• Nodes: with the columns: node_ID and Group as you uploaded them.
• Orders: with the columns: Order_ID and Order_Text (I added it just to show some data)
• Groups: with a single column, Group, containing the S1, S2, S3 and S4 selection groups; I don't see any benefit keeping this table.
• Orders_Groups: This is the Link Table handling the association between nodes and orders on the selection field.

The Link Table, Orders_Groups seamlessly associate the Nodes and Orders data based on the selections.

There are some topics in the Qlik Community explaining Link Tables, I added some links to them in this reply:

• Update to Link Table rules 
• How to remove Synthetic Key using Concatenation & Link table in QlikView? 
• Advantages of Link Table  (the accepted solution has a nice PDF file)
• Data Model with 2 fact tables  (it has a couple of nice PDF files as well)

I am attaching a demo application for your question.

Hope this helps,

[ArnadoSandoval]

@ZuzJsk007 

I did not see the attachment, do it again here!

hth

[Ksrinivasan]

hi,

Check the result by this script: as you mentioned When selecting node_id = 2, then Order_ID = 2 and 4 are selected 

Nodes:
Load * Inline [
node_ID, Group_N
1, S1
2, S2
3, S3
4, S4
];

NoConcatenate

Orders:
Load * Inline [
Order_ID, Order_Text
1, Order_1
2, Order_2
3, Order_3
4, Order_4
];

NoConcatenate
Orders_Groups:
Load * Inline [
Order_ID, Group
1, S1
2, S1
2, S2
3, S1
3, S3
4, S1
4, S2
4, S4
];

Result of this script is below: as you known

Group:
Load * Inline [
Group
G1
G2
G3
G4
];

NoConcatenate
Groups:
Load * Inline [
Group
S1
S2
S3
S4
];

NoConcatenate

Nodes:
Load * Inline [
node_ID, Group
1, S1
2, S2
3, S3
4, S4
];

NoConcatenate

Orders:
Load * Inline [
Order_ID, Order_Text
1, Order_1
2, Order_2
3, Order_3
4, Order_4
];

NoConcatenate
Orders_Groups:
Load * Inline [
Order_ID, Group
1, S1
2, S1
2, S2
3, S1
3, S3
4, S1
4, S2
4, S4
];
Exit Script;

regards,

Ksrinivasan

[ArnadoSandoval]

Hi @Ksrinivasan 

Why did you copy my script and screenshots? Would you please explain?

[Ksrinivasan]

Hi. I am extremely sorry. I thought it was ticket raiser. So,  i was mentioned as it's and modified one which gave his result . Accept my apology. 

Ksrinivasan.

[ZuzJsk007]

Wow, thank you very much! it's so brilliantly easy! It always amazes with Qlik Sense that the solution is so straight forward even though I expect something difficult 🙂

[Ksrinivasan]

Hi, Zuzjsk,

Which one is meet your requirement. Either first screen shot or second screen shot.

Ksrinivasan