Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
FakeJupiter
Creator
Creator

Synth Table Despite Autonumber Keying

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

Labels (4)
1 Solution

Accepted Solutions
FakeJupiter
Creator
Creator
Author

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

 

View solution in original post

8 Replies
edwin
Master
Master

just drop Y1 and M1 from one of the tables 

try that

Vegar
MVP
MVP

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
Creator
Creator
Author

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
Master II
Master II

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

 

Learning never stops.
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%....

 

Thanks & Regards,
Mayil Vahanan R
FakeJupiter
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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.