Dual() forces distinct when numeric value is same?

[rothtd]

I found yesterday that when using the dual function with differing text values and identical numeric values you get some interesting behavior:

// text values are different, numeric are same
Data:
LOAD
dual(F1, F2) as Value
INLINE [
F1, F2
Dog, 1
Cow, 1
Bird, 1
];

The result is that we have three records in our table, all identical: text=Dog, numeric=1.

If we ensure the numeric values are different we get what we would expect:

// text and numeric values are different
Data:
LOAD
dual(F1, F2) as Value
INLINE [
F1, F2
Dog, 1
Cow, 2
Bird, 3
];

I’m going to guess that this behavior is due to QlikView internal compression algorithm of only storing a single value once and then pointing to it multiple times if needed. My guess is that in the first example QlikView recognizes all three records as identical due to their identical numeric properties, and thus stores it once and points to it three times (this explains why we get three records but they are all erroneously identical).

Anyone else want to weigh in on this curious behavior?

[Gysbert_Wassenaar]

You're not the first to notice: Dual values are good, but...

And this behaviour makes complete sense to me. The same number cannot have two different textual representations. Don't mistake a dual value with a record. It's not. It's simply a value with both a numeric and textual representation.

Let's put it this way... We use arabic number notation these days: 1,2,3,5...10 etc. We used to use a roman notation: I,II,III,V... X etc. Now suppose that in the period the roman notation was dominant some clever dude decided it would be good idea that I, II and III would all represent the value 1. Does that make the same kind of sense as having Dog, Cow and Bird representing 1?

[Peter_Cammaert]

Gysbert, any idea why your link http://community.qlik.com/message/403570#403570 is blocked?

Peter

[swuehl]

Because it is in private group 'Data Quality', where you first need to apply for membership to see the threads:

Data Quality

(Sara Leslie would be good if the forum would actually point to the application site instead of giving an error...)

But the content of this thread is not much more detailed as what Gysbert explained anyway.

[tresesco]

Agreed that it makes sense, and guess it would have been more meaningful, if in such scenario, QV would have considered some algorithm to take all the texts into consideration with an incremental internal numeric represenation, may be like:

LOAD
dual(F1, F2+0.1*Autonumber(F1&F2)) as Value
INLINE [
F1, F2
Dog, 1
Cow, 1
Bird, 1
];

[Anonymous]

Thanks for the heads up- I checked all the links and they work for me. But this is because I have membership to that group. If you feel that the thread is not sensitive and should be available for all public viewers even in non loggged in status I can move it to this forum with permission from: Gysbert Wassenaar.

[Gysbert_Wassenaar]

Hi Sara Leslie,

You have my permission, but I think you mean Ralf Becher instead of me.

[swuehl]

Hi Sara,

my comment was more about this issue in general (like a feature request to the forum), not so much about the specific content:

- I often post links to existing posts, to avoid just manually repeating what have been discussed before

- Sometimes these links refer to a private group discussion (like the one above, though I haven't posted this one in this case), and I am most of the time not aware of this (just haven't the time to check the origin in depth)

- Thus the accessibility of the thread is depending on the access rights of the user who follows the link

I am all fine with this (assuming we need these private groups on this forum), but

- if the user has not the access rights for this private discussion group, it seems he/she just get an unspecific error message (I have noticed this several times now, not only with this link above)

- I would prefer / suggest that the error message is either stating the exact reason why the post can't be accessed, or even better redirect the user to the page where he can apply for member ship to this group (of course also additionally stating that the post of interest can only be accessed after membership has been granted).

Hope this clarifies my previous comment.

Regards,

Stefan

[Peter_Cammaert]

Or maybe even simpler: mark a link to restricted content with a special icon (a red balloon for example). Then both the poster as well as all visitors will get notified of the fact that this information "isn't really meant for everyone".

Thanks all,

Peter