Avoid Synthetic Key

[RTM]

Hi everyone!

I'm trying to insert a new orders table in my document and I'm having difficulties...

I manage to do it, but it created a synthetic key table. I don't think it's a problem BUT, the relations are not occurring as I need them. The thing is, the synthetic key works ONLY if all my fields match (Operation, Salesman, etc), and sometimes, one field may change, as the order is transformed into sales...

I need a way to force only the relationship between sales and orders. I only related the order table with all the other tables because I didn't want to load a customer table (and all others) twice in the same document, also it's easier to apply filters in just one "master" customer table than have a sales.customers table and order.customers table containing the same data.

Below it's how was my data model before I imported the order table, and how I need it to be.
The red arrow is the relation that should be enforced, despite mismatches on the other fields.

Is it possible? 

Thanks.

[Ksrinivasan]

hi,

you use Qualifier and Unqualifier function to avoid synthetic key

link below for your ref:

https://www.youtube.com/watch?v=IE7pgbp1Ob4

ksrinivasan

[Rodj]

As you are probably aware synthetic keys occur because you have two or more fields in a table with the same name as another table. They can often be avoided by simply commenting out or renaming fields, especially when you have tables with parent/child type relationship such as you appear to have been sales and orders. In your case however you haven't give us enough to go on, it appears that Sales and Orders have all the same fields. It seems likely however that Orders has the more detailed data and is probably where you want to keep all the associations. If that's the case I would comment out the fields from the Sales table (OrderID, ItemID etc.) as they are already existing and associated in your model. When you look at Sales rows you'll still have all those associations via the association with the Order table.

This is where the Qlik associative model works quite differently to a SQL set-based logic.

[Vegar]

You can solve this as mentioned my our colleagues above, making sure that the field names are unique for all fields except for the field where you want an association. 

Another approach is to concatenate the two transaction tables into one. 

Trsnsaction:

Load *, 'Sales' as TransType From Sales;

Concatenate Load *, 'Orders' as TransType From Orders;

By this approach you can share the dimension table between both orders and sales.

Both solutions have their own benefits and drawbacks, so which to choose depends on analytic needs and personal flavours? 

[RTM]

I can't just use qualify or change the field names without losing the relations I need.

Sales and Orders are two independent tables. An order may not become a sale, and I can have a sale without an order. So, Sales and Orders won't match all the rows.

But, let's say I qualify the Orders table. Will I have to load again all the other tables as well, qualified to the Order table?

Like, will I have a sales.customer, and a order.customer table at the end?
I was hoping to avoid needing to load the same table twice.

[RTM]

I can't just use qualify or change the field names without losing the relations I need.

Sales and Orders are two independent tables. An order may not become a sale, and I can have a sale without an order. So, Sales and Orders won't match all the rows.

But, let's say I qualify the Orders table. Will I have to load again all the other tables as well, qualified to the Order table?

Like, will I have a sales.customer, and a order.customer table at the end?
I was hoping to avoid needing to load the same table twice.

[marcus_sommer]

Just follow the suggestion from Vegar and concatenate both tables. Even if the resulting table is more or less asynchron and/or have a different granularities it's not in general a show-stopper - it may of course require some further transformation but each possible alternatively will be more complicated and causing more efforts. Therefore just try it - in most cases it worked very well.

- Marcus

[Ksrinivasan]

hi,

try this

https://data-flair.training/blogs/qlikview-synthetic-keys/

ksrinivasan