topic Re: How to remove Synthetic Key in App Development

How to remove Synthetic Key

kmmqlick — Sun, 18 Sep 2022 01:41:56 GMT

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.

Re: How to remove Synthetic Key

Vegar — Sun, 18 Sep 2022 06:35:54 GMT

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.

Re: How to remove Synthetic Key

RafaelBarrios — Sun, 18 Sep 2022 15:31:51 GMT

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,

Re: How to remove Synthetic Key

kmmqlick — Sun, 25 Sep 2022 15:36:16 GMT

Thanks @Vegar and @RafaelBarrios

Both works to me I went with RafelBarrios.