Removing one field from a synthetic table

[stig1984]

I have the following data model which has a synthetic key.

The synthetic key is working well for me and actually is providing the links that I need with the exception of the JOB_DATE field.

Due to the fact that I am using each of these fields as filters to provide comparison across goods in and sales I don't want to change names and the synth table has linked the rest of the data correctly - except that each filed is linked to job_date (date fields linked to the master calendar).

I tried to create a link table for JOB_DATE but this just gave me another table linked to the synth table.

Can I force JOB_DATE to form a separate synth table or is there a better solution.  I have read most of the post and questions on synth tables and linked tables but they do no seem to meet my requirements.

Script is attached.

Many thanks in advance for your help.

[othniel2014]

Try this:

See attached file.

In qlikview you can not have two or more tables with two or more fields with exactly the same name,

and all fields with exactly the same name, within qlikview are primary keys.

What I did was create an extra table to link your repeating fields, I do not know how optimal is this, or if you work for what you ask.

It seems to me that either you have not read the information provided to it in the last comments or you do not understand the right way.

is a little different logic entity-relationship model within qlikview but exactly the same thing at a time.

Greetings.

[stig1984]

Thanks - I have considered that but I have more fact tables to go in (such as production which will turn a goods in product into a despatched product with a different consignment number and various inspection details).

It is probably a good solution for others but I worry about the data integrity for my whole model given our process flows (fresh produce industry so can be quite a messy flow).

[stig1984]

Many thanks for this.

Unfortunately it's not working for me - too many links between goods in and sales are now broken so I have sales without product links and where links exist I still have the date issue.

It's a good example of how to do a link table though so I'll use it as starting point for experimenting.

I particularly like the different tabs for the script using the ///$tab **** - hadn't realised you could do this and it makes it much easier to read.in the script editor

[othniel2014]

What is the primary key in both table?

suddenly this helps the link not break.

By what fields you identify when a line of "Sales" is a line of "GoodsIn"?

[effinty2112]

Hi Stephen,

It looks like you've got a big job on your hands. From what you say concatenation of these fields won't be the final answer but it may be a simplification to the datamodel that will help you reach your goal. More trivially I would left join your RatesByDate table to your calendar, that seems a natural place for that data to belong.

Would you consider restricting the scope of what you're trying to do with one qvw document? Trying to bring a lot of data from different modules of an ERP system (Production, Sales, Inventory, etc) together into a single QlikView document would be challenging to say the least.

You could spread the work over a number of qvws and maybe this would keep the modelling of each document's data manageable.

If you create a number of qvws you can use document chaining to go from one document to an other while passing selections.

However you do it I it works for you.

Cheers

Andrew

[stig1984]

We get product in with a unique key (consignline) per product purchase order line.

This product may or may not go through production (not in data imported yet - extra complication I've been avoiding so far) which will change the product size and give it a new unique key (inv_det_cons in sales data).

I need to use the sales data to report but need to reference data from the goods in data.  the consignline in the sales data refers to the original goods in key, not the new key that was actually despatched (due to production in the middle).

For example - goods in of 100 boxes cases of apple size 100 on consignline of 100001/1

     Production is completed on this to make it into 5000 cases of apple 20x5 on inv_det_cons of 200002/1.

     I'll need to report that we sold 5000 cases of apple 20x5 and reference what was used to make it, what supplier it came from and all the associated costs of 100001/1.

     To complicate this each pallet sold could have more than one goods in consignline involved.

[stig1984]

I'm going through the original data now to see if I can concatenate them (if I'm careful how I do it to ensure that goods in and sales products don't get confused).

The more I look at it the more I'm liking it as long as I design it right.

Might be a few more days work yet though.

[stig1984]

Many thanks to everybody above.

With a combination of these answers and the following:

Canonical Date

Linking to two or more dates

Joins and Lookups

Concatenate vs Link Table

I have achieved the following:

I have attached the script for anyone else to steal and amend as they see fit.

Many thanks to everyone.