Strange Section Access mystery

[stascher]

Hello,

We're seeing a strange situation with our Section Access that we've never seen before. 

We have a Subjects table in our database with a field (SubjID) who's data type is (PK, int, not null). The values are straight forward. For example: 

SubjID	Name
1	Bob Smith
2	Jim Longley
3	Sarah Whitely

Our section access query is:

SQL select 'USER' as "ACCESS", upper('MYDOMAIN\MYUSERID') as "USERID", '*' as "GROUP", null as "OMIT", SubjID as "Subject"  from Subjects;

Our application body has the following query:

[Subjects]:

SQL select SubjID as Subject, Name from Subjects;

The above does not work; the table is read but I get no access to the data. However, if I change the above query to the following:

[Subjects]:

Load *, num(SubjID,'0') as Subject;

SQL select SubjID, Name from Subjects;

The above does work. Any idea why the internal representation of the Subject field in the Section Access query would differ from the representation of the same field in the [Subjects] table, such that section access works in one case but not the other? 

 Thanks,

Steven

[marcus_sommer]

Datatypes is a matter of SQL tools which isn't implemented within Qlik. Qlik used a data-interpretation into numbers and strings respectively a mix of both. This interpretation is controlled by region-settings and/or the interpretation-variables and/or directly applied by functions like num() and text().

Further important - the first loaded field-value of field defines the interpretation and therefore the load-order and/or the available data will have an impact.

[stascher]

I understand that. The fact remains that the table contains only non-null positive integer values and so they should be getting loaded as non-null positive integer values in both queries. Region and interpretation settings should not affect simple integers; they're not dates, timestamps, monetary values, etc. And even if they were being "interpreted" the same interpretation should apply to both queries and thus Section Access should work. Two similar queries of the same field in the same table should produce the same values, no?

[rwunderlich]

Doesn't the "Subject" field have to be upper case to act as a reduction field?

SubjID as "SUBJECT"

-Rob

[stascher]

No. Plus I tried that and it didn't make a difference. Thanks though.

[marcus_sommer]

No, not mandatory. Qlik doesn't load a table as the table/query looked within the source else it is loading n fields in separate system-tables and each previous/afterward load of the same field could impact the field. Therefore the above mentioned load/data order could have an effect.

Another thought may go to used driver - an integer in the data-base might in the output be converted into a string, for a lot of reasons like not knowing/handling all datatype-parameters or just ignoring/removing this information. Are the database(s) + drivers and their releases identically? If not there would be a higher likely that they are causing the issue.

[rwunderlich]

Are you loading Subject anywhere else in the app? If so, what does that LOAD statement look like?

-Rob

[rwunderlich]

This may be related https://community.qlik.com/t5/New-to-Qlik-Analytics/Number-showing-as-Text-but-showing-as-Number-if-...

[stascher]

No. We can replicate this in a test app which is basically just the Section Access and the Subjects table load, nothing else. 

[stascher]

A few more observations:  

1) If the casing of the variable references in the Section Access and in the Section Application matches, then section access does not work. However, if the casing of the variable does not match, then section access does work! It doesn't matter which variable is UPPER and which isn't, as long as they don't match, it'll work. 

2) The casing of the variables in Section Access and in the Section Application can match but only if the num() function is used to change the format of the variable's value in the table loader. 

These observations lead me to theorize that the Section Access query is loading data with one datatype/format and the data model query is loading the same variable with a different datatype/format.    I think when section access goes to link with the model, it takes the variable you specify and searches the data model for an instance to link to.. if it doesn't find an exact (case-sensitive) match, it links using the datatype/format of the variable it does find. But if it does find an exact (case-sensitive) match, it assumes that the data types/formats match.. but they don't. That's why when we have unmatched cases, things work, but when we have matched cases, they don't.  We can override the underlying datatype/format problem by using the num() function; which then allows for matched casing.