Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor II

Avoiding Synthetic Keys in Multiple Data Connections

Hello!

I have 5 excel sheets I am using for an app and each sheet has the following columns:

Year

Month

Quarter

PScore

HScore

Obviously, they cause a synthetic key because they are duplicated for the most part. I can't change the format of the excel sheet but is there something I can do in Qlik to avoid this? Would renaming them be the key to avoiding it?

Thank you1

Updated file

1 Solution

Accepted Solutions

Re: Avoiding Synthetic Keys in Multiple Data Connections

In script tabs :

  • 2018 Humidity April
  • 2018 Pressure March
  • 2018 Pressure April

Replace

    Final:

  with

    Concatenate (Final)


2018 Humidity March as is, as that will be creating the Final table to later be concatenated onto.

This should result in a single table data model.

For the 4 data loads from each xlsx sheet it may be useful to add a Source column :

'Humidity March'     as     [Source]

You may need it in your front end viz's, although I not check them to see if the need is there.

16 Replies

Re: Avoiding Synthetic Keys in Multiple Data Connections

Renaming them would probably eliminate the synthetic keys, but would mess up associations and selections.

I would look at concatenating the data from the 5 excel sheets into a single Fact table.

sasiparupudi1
Honored Contributor III

Re: Avoiding Synthetic Keys in Multiple Data Connections

use concatenation.. ex

t1:

Load

Year

Month

Quarter

PScore

HScore

From excel Sheet1 ;

Concatenate(T1)

Year

Month

Quarter

PScore

HScore

From Excel Sheet2;

hammermill21
Contributor II

Re: Avoiding Synthetic Keys in Multiple Data Connections

Thank you!

I'm having trouble actually putting it into my current script. I have attached a sample, if you could better direct me that would be great. This is something I"m going to be bumping into often due to the way they collect data.

THANK YOU!!!

vvira1316
Valued Contributor II

Re: Avoiding Synthetic Keys in Multiple Data Connections

Depending on other columns in your data concatenate is one option as BIll and Sasidhar have mentioned. If that is not suitable then you could create a composite key of the common columns and have them in a table of itself and have a composite key in each of the tables for a worksheet to create that link between them.

JustinDallas
Valued Contributor II

Re: Avoiding Synthetic Keys in Multiple Data Connections

I think you forgot your attachment.

hammermill21
Contributor II

Re: Avoiding Synthetic Keys in Multiple Data Connections

I attached it to my original question at the beginning of the thread.

Thanks!

Re: Avoiding Synthetic Keys in Multiple Data Connections

In script tabs :

  • 2018 Humidity April
  • 2018 Pressure March
  • 2018 Pressure April

Replace

    Final:

  with

    Concatenate (Final)


2018 Humidity March as is, as that will be creating the Final table to later be concatenated onto.

This should result in a single table data model.

For the 4 data loads from each xlsx sheet it may be useful to add a Source column :

'Humidity March'     as     [Source]

You may need it in your front end viz's, although I not check them to see if the need is there.

hammermill21
Contributor II

Re: Avoiding Synthetic Keys in Multiple Data Connections

Hey Bill!

Thanks that worked and removed all my synthetic keys.

Though now for my Humidity count it's doubling and I'm wondering if that because of it being my main scrip tab?

The Pressure count is perfect though.

Re: Avoiding Synthetic Keys in Multiple Data Connections

Did you create the [Source] field ?

You may well need to do a bit of Set Analysis in your expression to only calculate against appropriate [Source] data.

If after adding the [Source] field you can't suss the expression, then post your latest qvf so I can have a look.

Community Browser