topic Re: Removing Synthetic Keys in QlikView

Removing Synthetic Keys

Mon, 14 Dec 2015 14:45:38 GMT

Hi everyone,

I'm new to using Qlikview and have been using it for about a week now.

I'm developing a dashboard that shows the rates using Patient ID's as the numerator and denominator.

I successfully created table boxes that separate Year, Campus, Months, and Infections that when selected show the correct values for the numerator and denominator.

However, I have 1 synthetic table.

Background Info: I have 2 separate tables in the script in which I get the numerator and denominator data. Table A provides numerator, while Table B provides denominator. I have 4 columns from those tables that I link together in order to get those numbers, by qualifying all data and then unqualifying those specific 4 columns.

There is definitely a better way to model the data, and was wondering if there were any suggestions on how to remove the synthetic key, or model the data differently.

Thank you for your help.

Re: Removing Synthetic Keys

Mon, 14 Dec 2015 15:58:59 GMT

Hi,

Try to create a unique key ( only one field ) to link the tables. If you only have a field with the same name in both tables, you won't get the synthetic key.

Any further help only with the visualization of the model.

Best regards

Re: Removing Synthetic Keys

datagrrl — Mon, 14 Dec 2015 16:03:59 GMT

I am a big fan of creating composite keys.

Field1&'|'&Field2&'|'&Field3&'|'&Field4 as %KeyField

From the resident table then dropping the original table.

I am open to other suggestions though, so I would be interested in hearing what people say.

Re: Removing Synthetic Keys

Clever_Anjos — Mon, 14 Dec 2015 16:05:54 GMT

You don´t neet to remove it IF you are not experiecing performance issues Synthetic Keys

But you want to remove anyway, use this solution: Concatenate or Link Table?

Re: Removing Synthetic Keys

engishfaque — Tue, 15 Dec 2015 03:19:50 GMT

Dear BillJackson,

Create link/bridge table to remove synthetic table, here is a tutorial:

Link Table in QlikView | Learn QlikView

Or,

Just rename to 5 fields with new name using clause "AS" in edit script,

Example:

Salary as EmpSalary

Note: You can rename fields using clause QUALIFY and UNQUALIFY, AS, RENAME.

Kind regards,

Ishfaque Ahmed