Discussion Board for collaboration related to QlikView App Development.
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.