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.
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
ya,the result is correct!
wht's ur expecting output?
Both keys must be same. I mean there is year is common so will get data? or product has to same?
Example: 2013C from table1 and 2013X from table2.
I mean there is year is common so will get data? or product has to same? --no you can't get as per ur current keys.
if u want to get ur products based on same year,u have to modify ur keys first,because this case u key is combination of year & product.
You can use
left(Data,4) as Year
in both tables to get Year value as key.
why do you want to create a composite key ? because on the synthetic key ?
Hi Nihhal,
In the current scenario both the year and key should match, then only those records will be joined correctly. If you just want to consider year only then join both the tables by year
Table1:
LOAD
Left(Key, 4) AS Year,
,
,
,
FROM Table1;
Table2:
LOAD
Left(Key, 4) AS Year,
,
,
,
FROM Table2;
When you execute the above script both the tables are joined by year, so you will get this row as match.
2013 xxxxx 2013
Hope this helps you.
Regards,
Jagan.
Hi Jagan,
2013C, 2013X data shows by selection of year = 2013. In the scenes both composite key total values should not be same.
For example:
Year&Product&Customer id as key (Table1)
Year&Prodcut as key (Table2)
Here when i select Year it shows data from 2 tables
if i select Product it shows data from 2 tables
if i select Cutomer id it shows data from table1 itself.
Am i right?
Nihhal
Hi Shiva,
2013C, 2013X data shows by selection of year = 2013. In the scenes both composite key total values should not be same.
For example:
Year&Product&Customer id as key (Table1)
Year&Prodcut as key (Table2)
Here when i select Year it shows data from 2 tables
if i select Product it shows data from 2 tables
if i select Cutomer id it shows data from table1 itself.
Am i right?
Nihhal
wht ever the result is displaying as per the current selections are is absolutly right!
i didn't ding customer id in table 1?whr it's?
and u can tell wht is ur expected out put here?