Synth Table Despite Autonumber Keying

[FakeJupiter]

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

[edwin]

just drop Y1 and M1 from one of the tables 

try that

[Vegar]

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);

[FakeJupiter]

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.

[pradosh_thakur]

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

[MayilVahanan]

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

[FakeJupiter · Accepted solution]

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

[FakeJupiter]

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.

[FakeJupiter]

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.