Avoiding Synthetic Keys in Multiple Data Connections

[hammermill21]

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

[Anonymous]

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]

use concatenation.. ex

t1:

Load

Year

Month

Quarter

PScore

HScore

From excel Sheet1 ;

Concatenate(T1)

Year

Month

Quarter

PScore

HScore

From Excel Sheet2;

[hammermill21]

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]

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]

I think you forgot your attachment.

[hammermill21]

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

Thanks!

[Anonymous · Accepted solution]

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]

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.

[Anonymous]

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.