topic Synth Table Despite Autonumber Keying in QlikView

Synth Table Despite Autonumber Keying

FakeJupiter — Sat, 16 Nov 2024 17:43:22 GMT

Hello everyone,

I've been stuck on this issue for too long, so I'm turning to Qlik community for help.

To simplify, I have a similar script:

Table1: year(Date) as Y1, month(Date) as M1, Product1 Account, User, Date, Autonumber(year(Date)&month(Date)&Applymap('ml_Product1',Product1,'error')) as Date_Key From (irrelevant); Table2: Product2, Year2 as Y1, Month2 as M1, TransactionAmount, Autonumber(Year2&Month2&(Applymap('ml_Product2',Product2,'error')) as Date_Key From (irrelevant);

The problem is this produces a synthetic table between the two tables. I don't understand why is there a need for the synth table, if Autonumber in Table2 already includes the Y1 and M1 fields.

But anyway, I understand the fix is to remove Y1 and M1 fields from Table2. However, doing so produces wrong results for months January and November of year 2020 if I make a chart with dimensions Y1, M1 and an expression of '=sum(TransactionAmount)'. I have no idea why is that the case. The results are correct for all other months.

Looking forward to your input.

Thanks,
Ray

Re: Synth Table Despite Autonumber Keying

edwin — Wed, 09 Dec 2020 19:58:11 GMT

just drop Y1 and M1 from one of the tables

try that

Re: Synth Table Despite Autonumber Keying

Vegar — Thu, 10 Dec 2020 05:48:25 GMT

As @edwin says or like this :

Table1:
year(Date) as Y1,
month(Date) as M1,
Product1
Account,
User,
Date,
Autonumber(year(Date)&month(Date)&Applymap('ml_Product1',Product1,'error')) as Date_Key
From (irrelevant);

Table2:
Product2,
Year2 as Y2,
Month2 as M2,
TransactionAmount,
Autonumber(Year2&Month2&(Applymap('ml_Product2',Product2,'error')) as Date_Key
From (irrelevant);

Re: Synth Table Despite Autonumber Keying

FakeJupiter — Thu, 10 Dec 2020 07:22:49 GMT

Thank you guys, but as I said, the problem is, if I drop those two fields, wrong results are produced (see the bottom of my original post). Any ideas why that might be the case?

And I cannot call one Y1 and M1 and the other Y2 and M2, because I need them both in the same pivot chart.

Re: Synth Table Despite Autonumber Keying

pradosh_thakur — Thu, 10 Dec 2020 07:34:02 GMT

1: Check the subset ratio of the Automumber key, that might be the issue for the mismatch with and without Syntetic key

2: Use right/left keep instead if feasible for your problem

-Pradosh

Re: Synth Table Despite Autonumber Keying

MayilVahanan — Thu, 10 Dec 2020 07:35:03 GMT

HI @FakeJupiter

You can try with link Table concept for this scenario.

FYR: https://www.learnallbi.com/link-table-in-qlikview/#:~:text=When%20you%20have%20multiple%20Fact,rows%20of%20the%20two%20tables.

Re: Synth Table Despite Autonumber Keying

FakeJupiter — Thu, 10 Dec 2020 12:08:41 GMT

Thanks everybody for your help.

I finally figured out what was the problem why there was wrong data in January and November if I removed the Y1 and M1 fields from Table2.

This issue, of course, was in the Autonumber, as some entries were matched incorrectly.
The product I put into Autonumber field with Applymap was a number - from 1 to 31.
So, for instance, in January of 2020 the Autonumber function may have gotten this information: "2020117" (year&month&product). The thing is that in november it got the same input, but month was 11 not 1 and product was 7 not 17. Therefore there were some double entries.

I fixed this by adding 100 to the month field, so that all month names are of 3 digits.

Autonumber(year(Date)&(100+month(Date))&Applymap('ml_Product1',Product1,'error')) as Date_Key

Re: Synth Table Despite Autonumber Keying

FakeJupiter — Thu, 10 Dec 2020 12:09:37 GMT

Subset ratio was not the issue, but after doing some reading I learned more about it, so thank you! See the solution below, if you're interested.

Re: Synth Table Despite Autonumber Keying

FakeJupiter — Thu, 10 Dec 2020 12:10:29 GMT

Link Tables would not have resolved the issue, as it would inherit the same problem I had with autonumber, but thank you, I read the article, seemed useful!

See the solution below, if interested.