Problem with multiple tables

[#1]

Dears

Here ia nother big problem that I'm facing.

I have two excel files in my QlikView application.

The first one is called Company.Mov.xls and contains all cash monvimentation from all companies of our group.
The secound one is called Company.Region.xls and contains the company name and it's region.

The Company.Mov.xls file does has all the cash movimentation for all of our companies, so it contains the company name but not the company region, and for this reason I need the other excel spreadsheet. In both of than I have the collumn Company, in order to create the correlation.

I created 2 "List Boxes" one to Company name and the other one for Region. Everything is working good, if I choose the company by selecting one option in the company name, she sistem returns me all the calculated information like (Numer of claims, total claimed, revenue, cost...) but if I clear everything and choose one spefic region, like North America, the sistem do not return anything. All values are returned EMPTY.

So, what's going on?

How can I fix this problem?

Thanks for your attention

[Anonymous]

Please ensure that the proper joins are established (CTRL + T). If not, you may check the column names as QlikView is case sensitive.

Regards,

Dinesh.

[#3]

I think it's right

There is no problem with the correlations

[#4]

Check my correlations view.
I don't know if it's perfectly right or not

I don't know what is this $Syn 1 Table

Could you help me?

[Anonymous · Accepted solution]

They are synthetic keys when you need to join based on 2 columns. Are you sure you want to join based on these 2 columns?

Regards,

Dinesh.

[Miguel_Angel_Baeyens]

Hello,

The $Syn Table means that there is more than one field named identically in more than one table, "Shop Name" and "Type". Since QlikView associates tables where fields are named alike, it's linking "Report" and "SCDatabase" by those two fields. It doesn't mean there is any error or issue.

If that linking is not correct, then you will have to rename the fields that is the actual key between them in the script, so both tables are linked by that field.

Hope that helps.

[erichshiino]

This is a synthetic key.

QV generated this automatically because there is more than one field in common between two tables. Even if this relationship is correct, this may have an impact on your performance.

You should remove it by making only one field in common between the tables.

You can concatenate the fields to make a key:

Load Shop Name & '|' & Type as key, //create this in both tables

Claim #,

[Other fields... ]

resident ...

[gauravkhare]

Hi kelly,

as you read from the earlier post that you have developed synthetic keys in your applications.. these synthetic keys should be avoided at all costs becuase when it comes in for handing large amount of data then these keys while definately pose a threat to your application by giving some absurd output.

You can remove your synthetic key by renamimg those fields which are duplicating by either renmaing them or you can also use qulify and unqualify depending on your requirments.

[#9]

YOU GUYS ARE THE BEST!

JUST IT

COMPLETELY SOLVED MY PROBLEM!

[Miguel_Angel_Baeyens]

Hello Gaurav,

Synthetic keys by themselves don't suppose a threat for any development. They take time to calculate as any other composite key would take time to calculate and link.

I strongly recommend to check this post where synthetic keys are discussed in depth.

If your datamodel is correct, syn keys work fine, and may save coding time.

Regards.