Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kmmqlick
Contributor III
Contributor III

How to remove Synthetic Key

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.

Labels (1)
1 Solution

Accepted Solutions
RafaelBarrios
Partner - Specialist
Partner - Specialist

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,

View solution in original post

3 Replies
Vegar
MVP
MVP

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
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

Thanks @Vegar  and @RafaelBarrios  

Both works to me  I went with RafelBarrios.