How to remove Synthetic Key

[kmmqlick]

Hi All i am having 2 tables with following details

Table1 : This table detailed analysis of sales, based on only 1 source

Description

Region( 2 Regions)

Year(2 Years)

Type(Per, Temp)

Sales

Table2: This table is consolidated sales based on regions (Some additional Regions also) from different sources

Source

Region (6 Regions)

Year(2 Years)

Type(Per, Temp)

Currency

Values

when i loaded these two tables forming a synthetic key with Region+Year+Type. How to remove the synthetic key from those two table and how to create association between two tables.

I want to use Region Year Type filters commonly in from both tables for filtering.

[Vegar]

I would consider to concatenate the two sources into one table.

Load

Region, Year, Type,Description, Sales, null() as Currency , 'Source1' as Source

From Source1;

Concatenate Load 

Region, Year, Type,Null() as Description, Values as Sales,  Currency , Source  

From Source2;

You can pinpoint which rows to calculate in each measure by using set analysis with a simple modifier on the Source field.

[RafaelBarrios · Accepted solution]

Hi @kmmqlick 

agreed with @Vegar 

but, if you still want to keep then in two tables, you can try creating a key

load

   autonumber(Region&Year&Type) as key,

   Description,

    Region as sales_region,

   Year as sales_year,

   Type as sales_type,

   Sales

from <whereever you are loading table1 from>

load

 autonumber(Region&Year&Type) as key,

  *

from <whereever you are loading table2 from>

as i did in table1, you can rename fieds in one of the table if you want to use both

or, if all values exists in both tables, just dont load from any, and by selectiong in one, the other table will filter also.

load

 autonumber(Region&Year&Type) as key,

   Description,

   Sales

from <whereever you are loading table1 from>

the autonumber is not mandatory, but for better performance you should always try to link tables  using numbers and not with text

best,

[kmmqlick]

Thanks @Vegar  and @RafaelBarrios  

Both works to me  I went with RafelBarrios.