Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
Creator III

How to start organise the following scenario?

1. There is a physical Machine - known by a number of different names - it also has a unique number.

2.     The physical Machine - exists in a physical location - known by a number of different names: for example: city, EM, Melb, East Melb,E Melb

3.      The physical machine - has a number of modes of working. Mode1, Mode2, Mode3, Mode4 - and those modes can be categorised as SuperMode1, which cosists of Mode1 and Mode2 and SuperMode 2 which consists of Mode3 and Mode4.

The system I am trying to implement will take values from:

Database1 - this only has the physical location and the superMode and the date and a couple of values.

Database2 - this has the physical Machine, with Mode1, Mode2, Mode3 - and the date and a couple of values.

Spreadsheet 1 - this has location and one mode only and a date and a couple of values.

So do I create a spreadsheet -with all machines, modes, alternative names etc?

Do I create 3 separate spreadsheets - a.machines and all alternative names b. location will all alternative names, c. modes with all althernative names

Tried Trial and error - almost worked.

Looked up incremental loads - that worked well

tried to understand the Date/Data Island - but I think I need to fix the spreadsheet/spreadsheets first.

Ideally I see it as - values from the Databases and spreadsheet - being able to be diced by location, Machine, Mode.

Thank you

Jo

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You first need to decide if the date field in both tables should really be a key between the two tables, or is it something different.

If it's a key, you can maybe live the synthetic key or create your own concatenated key.

Synthetic Keys

You may also consider changing the data model, for example concatenating the two tables ACHS and machines.

See also my previous link about multiple or single calendar tables.

View solution in original post

10 Replies
swuehl
MVP
MVP

I think you would need to create a consolidated dimension for phys.machine as well as for phys. location. i.e. fields that translate all different names to one 'common' name per phys. entity, might also be just a technical name not exposed to the user (who will select from the 'different names' field).

I believe someone needs to define this translation table.

Your tables are then linked by the 'common' name field (or maybe a composite key made of 'common' name, date, ...).

I haven't really understand your data model, so if you need more help, I think some sample lines of data would really help to understand.

josephinetedesc
Creator III
Creator III
Author

so you would create 3 spreadsheets or 1 spreadsheet?

swuehl
MVP
MVP

Not sure, since I haven't fully understood your requirements and data.

Could you upload a small sample or sample data?

josephinetedesc
Creator III
Creator III
Author

OK so the following works but!  I now need all the dates in the 3 tables, ACHSDateFull, ArchiveDate, DateFull to respond to the Calendar - If I call them all the same name then it causes problems and spaghetti links.  What can I do?  Hardcode the monthname?

Capture1.PNG

josephinetedesc
Creator III
Creator III
Author

yes I do not need separate calendars ...

I do not know how I would begin to do a bridging table ... or adata date island ...

josephinetedesc
Creator III
Creator III
Author

ok this is what i did - I learnt about Resident loads!

but: this leads to synthetic keys ...however figures seem to be in the right range. 

Should I live with the synthetic keys?  How can I get rid of them?

Jo

capture2.png

capture3.jpg

josephinetedesc
Creator III
Creator III
Author

I have this: which makes logical sense

Capture1.PNG

but if I try to do this - ie the machine table is joined to the calendar table I get a synthetic key.  I want to  delete the synthetic key how do I do the resident load when information needs to come from 2 tables?

capture2.png

Thank you

Jo

swuehl
MVP
MVP

You first need to decide if the date field in both tables should really be a key between the two tables, or is it something different.

If it's a key, you can maybe live the synthetic key or create your own concatenated key.

Synthetic Keys

You may also consider changing the data model, for example concatenating the two tables ACHS and machines.

See also my previous link about multiple or single calendar tables.