Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Which scenarios we go for 'composite key' except avoiding synthetic keys. can you give any example.
My question is both values (Year and Product) should be same in both key values to get data on selections.
For Example:
Table1:
LOAD
Year&Product AS Key,
*;
LOAD * INLINE [
Year, Product, Sales
2011, A, 10
2012, B, 20
2013, C, 30
];
Table2:
LOAD
Year&Product AS Key,
*;
LOAD * INLINE [
Year, Product, Rev
2011, A, 1000
2012, B, 2000
2013, X ,3000
];
Thanks
Nihhal.
Thanks for writing shiva.
There is no customer id just i given example.
I have confusion which scenarios we go for 'composite key' except avoiding synthetic keys. can you give any example.
Thank you.
Nihhal.
as u said,for avoiding synthetic keys u can use composite keys as technique.
for example ur have 3 tables,
table1 having 10 fields,out of 10 fields 5 are key fields
table2 having 15 fields,out of 15 fields 7 are key fields
table3 having 20 fields,out of 20 fields 3 are key fields
but,we have to map all these tables,here some of the key fields in table1 will match with tabl2 key fields and some of the key fields in table2 will match with table3 keys fields.
so,this is the case u have to create multiple key field by combining those fields from corresponding table.
note: pls mark whichever post helps to as helpful.
No, you're assuming that keys can do partial matching which they cannot. If all customer IDs have values, the keys will never match. and the two tables will be in separate universes (that is if you rename at least one set of
Product & Year fields, otherwise you'll have a syntehtic key as well).
If you want a simple solution, LEFT JOIN the second table to the first. Rows that have a Sales amount but no Rev value will have Null in the Rev field. Rows that have a Rev value but no Sales amount will have Null in the Sales field. Rows that have both will show both when you select the corresponding Year (& Product).
If you want to forcibly link with a key, create identical keys in both tables and rename at least one set of Product/Year fields. You will now have a problem with Dimension filtering because the only Product field left will only show values from one table. That's why you should extract in a second step all Product/Year pairs from both tables into a third table, together with their key values. Then drop both Product and Year fields from the original tables, as they will become Dimensions in the third table.
Peter
Table1:
LOAD
Year&Product AS Key,
*;
LOAD * INLINE [
Year, Product, Sales
2011, A, 10
2012, B, 20
2013, C, 30
];
Table2:
LOAD
Year&Product AS Key,
*;
LOAD * INLINE [
Year, Product, Rev
2011, A, 1000
2012, B, 2000
2013, X ,3000
];
Bridgetable:
LOAD Key,
Year,
Product
Resident of Table1;
LOAD Key,
Year,
Product
Resident of Table2;
Drop fields Year,Product from Table1;
Drop fields Year,Product from Table2;
Thanks Peter.