Data Modelling in Qlik Sense

[davenyrfajutag00]

Good morning everyone, hope you are good today!

 I am asking support about data modelling in Qlik sense in particular link tables.

Attached you can see how I managed to create a link table between these different fact tables but let us focus on two tables in particular which are Venduto and Ordinato_Cli.

Venduto is connected to the link table through a composite key (called VEND_KEY) that is formed this way:

TEXT(CUSTOMER_ID)& '|' & TEXT(ITEM_ID) & '|' & TEXT(DOC_TYPE) & '|' & TEXT(INVOICE_NUMBER) AS VEND_KEY

Ordinato_Cli is connected to the link table through a composite key (called ORD_KEY) that is formed this way:

TEXT(CUSTOMER_ID)&'|'&TEXT(ITEM_ID)&'|'&TEXT(DOC_TYPE) & '|' & TEXT(ORDER_NUMBER) AS VEND_KEY 

I also created two sheets in Qliksense: one sheet showing a table with all the invoices' information, the other one showing a table with all the orders'information.

Problem is when I select a particular invoice and I go to the order sheet there are no rows available as it Qlik does not recognise the order that generated that invoice: I think it is because I have to link the two tables in the data model connecting the two fields highlighted in green as you can see in the image attached.

I tried creating a new key connecting the two tables and then dropping these fields in the end to avoid circular reference error but it still does not work.

In the Venduto table: TEXT(CUSTOMER_ID)& '|' & TEXT(ITEM_ID) & '|' & TEXT(ORDER_NUMBER venduto field )&'|'&DATE(ORDER_DATE venduto field) AS VEND_ORD_KEY

In the Ordinato_Cli table: TEXT(CUSTOMER_ID)& '|' & TEXT(ITEM_ID) & '|' & TEXT(ORDER_NUMBER ordinato_cli field)&'|'&DATE(ORDER_DATE ordinato_cli field) AS VEND_ORD_KEY

Do you have any suggestions to work this around?

Hope I explained myself clearly.

Thank you in advance!

[HeshamKhja1]

Hi @davenyrfajutag00 ,

In your second composite key VEND_ORD_KEY:

• Are you sure the ORDER_DATE is date and not a timestamp?
• All the fields exist in both tables in the same format?

If both the answers are yes, then try and link with the order number only and see how it behaves.

I would like to know the cardinality as well. Is it N--N; that is; an invoice can have multiple orders and an order can have multiple invoices?

[Mark_Little]

Hi,

The issue is the your orders and Invoices and not joined on the Link Table, I don't know enough about your data to suggest how you would model this, But i would guess your Invoice data will have an order number in, Try using the Same key for both invoice and orders, Just on the invoice side using the field name holding the order number 

[marcus_sommer]

Don't use a link-table data-model else a star-scheme with a single (mostly per concatenating) merged fact-table by harmonizing field-names and data-structures as much as possible.

[davenyrfajutag00]

Hi thank you for your answer,

yes ORDER_DATE is a date and not a timestamp and I also made sure that all the fields exist in both tables in the same format.

Ok, I will try and see if it works.

Yes the cardinality is N-N so an invoice can have multiple orders and same way an order can have multiple invoices as you correctly said.

[davenyrfajutag00]

Hi 

ok thank you I will try this way too!

[davenyrfajutag00]

Hi thank you for your answer!

Could you please make an example of a star-scheme with a single merged fact-table?

[marcus_sommer]

Maybe something like this:

fact:
load OrderID, OrderLineID, Article, Value, 'Order' as Source from Orders;
concatenate(fact)
load OrderID, OrderLineID, InvoiceID, Article, Value, 'Invoice' as Source from Invoices;

It are mainly the same data - regardless if it's an order or an invoice or shipments/retour - just the state is different.