topic Re: resolve synthetic keys in QlikView

resolve synthetic keys

qlikhans — Mon, 15 May 2017 09:35:29 GMT

Dear

Please see attached

I want to show in 1 QV document actuals versus budget. Actuals separately works, Budget separately works, Combining Actuals and Budget creates synthetic keys and does not work properly. I have tried to resolve by qualifying. However unqualifying to much leads to the same problem.

unqualifying for example ACCT_NO, results in budget data only up to the month of uploaded actuals.

I need to see reports by (at least) Entity, by ACCT and by Date.

Who can support me?

Re: resolve synthetic keys

awhitfield — Mon, 15 May 2017 09:41:33 GMT

Morning Hans,

you can resolve this my creating a 'composite' key on both your Actuals and Budget tables. There's a worked example on the following post that should help you solve the issue:

how to create composite key‌

Best regards,

Andy

Re: resolve synthetic keys

qlikhans — Tue, 16 May 2017 09:44:49 GMT

Dear Andrew,

Thank you for your answer. I have tried seveeral options, but have not been fully succesful up to now.

May I ask you to check my (attached) sample files and help me in getting it right?

t.u. in advance

Hans

Re: resolve synthetic keys

qlikview979 — Tue, 16 May 2017 11:31:47 GMT

Are you expecting to remove syntactic in document. what is the link column between this table

Re: resolve synthetic keys

Anil_Babu_Samineni — Tue, 16 May 2017 11:41:46 GMT

From given qvw, Seems there is no Circumference Keys / Synthetic keys

Re: resolve synthetic keys

qlikview979 — Tue, 16 May 2017 11:55:36 GMT

May be

Are Expecting like below right

PFA

Re: resolve synthetic keys

qlikhans — Tue, 16 May 2017 12:37:30 GMT

Hi Anil,

t.u. for replying

the issue here is that after trying to resolve the synthetic keys, QV warns for ambigous results.

Re: resolve synthetic keys

ahaahaaha — Tue, 16 May 2017 12:59:07 GMT

Hi,

A pretty decent scheme, if only correctly linked tables (one to one and one to many). When organizing data, I usually try to plan/budget data and actual data in one table with a [budget] or [fact] flag.

Regards,

Andrey

Re: resolve synthetic keys

Anonymous — Tue, 16 May 2017 13:24:55 GMT

If you are getting synthetic keys it is because the tables have more than one way to reach each other. A table should only every have one foreign key within it; from looking at some of the photos shared, there are multiple columns that have the same name in other tables. One way to fix this is to rename them.

Do you have a sample data file?

Re: resolve synthetic keys

qlikhans — Tue, 16 May 2017 13:25:27 GMT

Dear Mahesh,

similar. In the end my output requirement is to compare the actual with the budget:

for example

Entity_NO

ACCT_NO

Sum(Amnt_ACT)

Sum([Month Budget])

difference

RepNameCat3

Sum(Amnt_ACT)

Sum([Month Budget])

difference

Entity_No	CC_NAME	Sum(Amnt_ACT)

the (left,7) positions of ACCT_CC = ACCT_NO. So ACCT_CC is more detailed than ACCT_NO. In your input the connection between ACCT_CC and ACCT_NO appears not to exist.

WOuld establishing that connection be a further step in resolving my issue?

If so, could you help me with that?

t.u. in advance for your help

Re: resolve synthetic keys

qlikhans — Tue, 16 May 2017 13:28:34 GMT

Hi Matthew,

it is in my reply to Andrew at 5:44

brgrd

Hans

Re: resolve synthetic keys

qlikhans — Tue, 16 May 2017 13:30:54 GMT

Hi Andrey,

I am open to any solution. And flaggin might be the quickest solution.

WOuld you be able to do that with the sample files that I posted in my reply to Andrew at 5:44?

brgds

Hans

Re: resolve synthetic keys

Anonymous — Tue, 16 May 2017 13:41:59 GMT

For actual table make this change:

Date as ActualDate

For Budget table make this change:

Date as BudgetDate

Let me know how you get on. If that doesn't work I have another idea

Re: resolve synthetic keys

ahaahaaha — Tue, 16 May 2017 14:03:32 GMT

I do not represent the content of your data, so I'm very much afraid to steer you on the wrong path. In the attached file, I brought all your data into one file. All comments in the script code. The script itself can only be used as a sample. For example, I joined the source tables Actual and COA with the left link, but I'm not sure if this is correct. Only you from the sense of your data can correctly collect them in one table.

I would be glad if something could help you.

Regards,

Andrey

Re: resolve synthetic keys

qlikhans — Wed, 17 May 2017 06:11:34 GMT

Hi Andrey,

I think this is going to work for me. The sample database is showing me promising results. I will apply your solution to my live data.

t.u.

brgrds

Hans