Filtering codes with button breaks with many numbers

[midnight1]

Hi,

context:
in my app in QlikSense i have a column with barcodes. Since those can have zeros at the beginning, I load them as Text in data load editor.

Users need to be able to paste some barcodes into app and the app need to filter it. They copy paste them from excel for example so the barcodes are separated with space, not commas.

How it works:

I have two Variables:

iCodes_VERSION2 which has empty definition
iCodesFormatted_VERSION2 with definition:

='"' & ReplaceRegEx(Trim(iCodes_VERSION2), '\s+', '","') & '"'

In the sheet i have "Variable Input" with Variable set to iCodes_VERSION2:

So when user enters some barcodes, the variables will become:
iCodes_VERSION2 = 590549354 590549982 00059849482
iCodesFormatted_VERSION2 = "590549354","590549982","00059849482"

i have two KPI's users need:
1. how many barcodes user entered

=if(Len(Trim(iCodes_VERSION2))=0, 0, 
  RangeSum(SubStringCount(ReplaceRegEx(Trim(iCodes_VERSION2), '\s+', ' '), ' '), 1)
)

2. how many of those pasted barcodes exists in report

=if(Len(Trim(iCodes_VERSION2))=0, 0, count({< GenCod = {$(iCodesFormatted_VERSION2)} >} distinct GenCod))

I also have two buttons:

1. Search button which filters those founded barcodes :

='("' & ReplaceRegEx(Trim(iCodes_VERSION2), '\s+', '"|"') & '")'

And after clicking on it it will filter the data:

2. The clear button just clears this filtering.

It work fine when user paste for example 1500 barcodes :

It properly counts barcodes and filters them after clinking on the search button.
The problem is that users need to enter much more barcodes but it breaks then. It counts but after trying to search, it selects all barcodes.

Please help, I don't know how to fix that 😢 The sad part is that it used to work just fine.

 

[marcus_sommer]

There is no direct error else the approach hits any limitation because the amount of values respectively the number of chars/bytes and/or the resulting parameters are too large. Some features are intentionally limited to n parameters/sizes (like nested if-lops) and many functions/features are restricted to underlying functions/libraries which accept only 32/64 bit sizes or certain data-types or similar stuff. It's rather unlikely to find ways to bypass such restrictions directly.

You may do a bit trial error with the single parts of your approach to detect the ones where it breaks and then by how many values and using it as starting point to substitute it anything else or finding a workaround.

Before diving deeper in the above mentioned stuff I would investigate:

• if the barcodes could be distributed to two variables or maybe n ones
• if the barcodes could be also numeric values within a second field - for example if there is no overlapping of values (numbers are needing lesser resources as strings and it avoids the need of any quoting to the values)
• if the barcodes could be grouped in some way - to certain product-groups and/or periods or any similar logic and main/sub-parts of the codes might be addressed separately
• any exclusion-logic could be applied to reduce the number of codes in beforehand (min/max code-values in regard to the selection-state and each code which is on the outside will be excluded)