Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
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.
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,