Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikers!
My name is Ignasi Mata and this is my first post, which unfortunattely is not to give an answer, rather to ask a question.
I have an issue regarding listboxes with aggr and if in the expression. Listboxes are workig "fine", but when I make a selection the "else part" disappears (if excluded) or does not appear as selected (if selected).
Here the script, and listbox field expression code:
Example_table: Load recno() as ID, chr(ceil(Rand()*26)+64)&chr(ceil(Rand()*26)+64)&chr(ceil(Rand()*26)+64) as Field_1, ceil(Rand()*10000) as Field_2, ceil(Rand()*10000) as Field_3, ceil(Rand()*10000) as Field_4, ceil(Rand()*10000) as Field_5 AutoGenerate(10000);
=if(aggr(count(ID),Field_1)<2,'Unique','Duplicate')//and 4 boxes more for Field_2-5; count(ID) as expression (proably irrelevant)
=if(aggr(count(ID),Field_1)>1,'Duplicate','Unique')//and 4 boxes more for Field_2-5; count(ID) as expression (proably irrelevant)
Screenshots as well:
These are selections for just only one field, if I start making selections in other fields, it's getting messier.
Thanks for your help!!!!! And not just this time, older posts have helped me a lot as well.
Cheers,
Igna.
EDIT: Data uploaded
Hi!
I have reach the functionality that I was looking for.
In any case, some details could (and should) be improved, as well as performance I guess, nevertheless I'm happy with the result that I got.
Thanks for your help and time!
Not entirely sure what your issue is, but try this
=if(aggr(count({<Field_1>}ID),Field_1)<2,'Unique','Duplicate')
Hi Ignasi Mata,
Yes, the listboxes not working very well, I had a similar issue.
In your casse you can do this:
Example_table: Load *,
If(aggr(Count(ID), Field_1) < 2, 'Unique', 'Duplicate') as Flag_Duplicate_1;
Load recno() as ID, chr(ceil(Rand()*26)+64)&chr(ceil(Rand()*26)+64)&chr(ceil(Rand()*26)+64) as Field_1, ceil(Rand()*10000) as Field_2, ceil(Rand()*10000) as Field_3, ceil(Rand()*10000) as Field_4, ceil(Rand()*10000) as Field_5 AutoGenerate(10000)
Cheers,
Dan.
Thanks a lot for your fast answers!!!
With "=if(aggr(count({<Field_1>}ID),Field_1)<2,'Unique','Duplicate') " the results do not disappear. But when I'm selecting in several boxes, selections are not highlited.
The script solution is not perfect suited for this situation because it's not dynamic and results should vary depending on selections.
Moreover I have just realized that in my concept sombething must be wrong, because the selections are no dynamic either. Nevertheless, what I'm trying to achieve is to search duplicates within duplicates (of course, different fields) in a dynamic way (or uniques values within duplicates) .
I have tried also "=if(aggr(count({1}ID),Field_1)<2,'Unique','Duplicate') " but this one is not dynamic and I'm having issues with the highlighted selections as well.
Thanks again for your help!
So, you want to select Duplicates in your multiple listboxes to show the IDs which are duplicate within multiple fields?
Yes, that's right.
I have being thinking about it and possibly, what I would need is a dynamic aggregation, but not entirely sure.
This might not work the way you are hoping for it to work. May be someone else can have a better way to do this
You are totally right, I have been trying hard for two days and I cannot find the right solution.
In any case, I will keep trying till I run out of ideas (right now, I have two) or till I get more desperate. In case I find a right answer, I will of course post it here.
Thanks again!!!
Hi!
I have reach the functionality that I was looking for.
In any case, some details could (and should) be improved, as well as performance I guess, nevertheless I'm happy with the result that I got.
Thanks for your help and time!