topic Re: Synthetic Key Help in QlikView

Synthetic Key Help

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Synthetic Key Help

sunny_talwar — Fri, 16 Sep 2016 18:34:03 GMT

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 ...

Re: Synthetic Key Help

krishnacbe — Fri, 16 Sep 2016 18:35:25 GMT

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.

Re: Synthetic Key Help

Fri, 16 Sep 2016 19:08:45 GMT

Thank you!

Re: Synthetic Key Help

Fri, 16 Sep 2016 19:08:55 GMT

Thank you!

Re: Synthetic Key Help

Fri, 16 Sep 2016 19:22:27 GMT

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?

Re: Synthetic Key Help

Fri, 16 Sep 2016 19:23:19 GMT

Hi Krishnapriya,

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

Thank you-

Re: Synthetic Key Help

sunny_talwar — Fri, 16 Sep 2016 19:25:50 GMT

How do you know which one should be 0s?

Re: Synthetic Key Help

krishnacbe — Fri, 16 Sep 2016 19:26:41 GMT

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.

Re: Synthetic Key Help

Fri, 16 Sep 2016 19:29:52 GMT

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.

Re: Synthetic Key Help

Fri, 16 Sep 2016 19:34:16 GMT

ok so if i make a pivot, then it should show correct results. Is it possible to sort on calculated field in the pivot?