Synthetic Key Help

[#1]

Hi Qlik Gurus,

I am loading two tables to qlikview, that have common fields. However, they don't seem to be joining correctly.

For example Client in table Retain and Contracting Firm Client Name in GFS is a common field (the spelling is the same), but does not appear so. Therefore, calculations and graphs are skewed and do not show correct results. Please advise to correct this issue.

[sunny_talwar · Accepted solution]

They should be called the same name for them to join properly. May be rename the field within GFS to be called Client as well

GFS:

LOAD YourFieldNames,

           ....

           [Contracting Firm Client Name] as Client

FROM ...

[krishnacbe]

Hi,

Rename Contracting Firm Client Name as Client, so that it will automatically link.

and to avoid Synthetic key rename the Personnel ID, Month & Year field.

[#4]

Thank you!

[#5]

Thank you!

[#6]

Sunny,

Now I have the following issue, if one client has one line (hours in particular) for one period in GFS table, but Retain table has 4 lines which should be combined.Then in a table it drugs one line to appear on multiple.

Ex. below

39 only should have been once, however retain table has 4 lines for the client (should be combined), then calculation of GFS is already wrong. 3 out of 4 should be zeroes.

How can I correct it?

[#7]

Hi Krishnapriya,

See my second question above in case you might know the solution.

Thank you-

[sunny_talwar]

How do you know which one should be 0s?

[krishnacbe]

Hi Nina,

It depends on how you join and which record you want to show. for example the latest hours or avg/sum can be used.

In Straight table what are the other columns used will make the duplicate records.

[#10]

i dont, i only know that in the data its only 39 hours, so when i sum GFS in qlik the result is much higher.