Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
just drop Y1 and M1 from one of the tables
try that
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);
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.
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
HI @FakeJupiter
You can try with link Table concept for this scenario.
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
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.
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.