Missing records while searching EAN / gencode

[Sebastian_Dec]

Hi,

I have problem with missing records while searching.
My raport have 2 GB and 54'172'765 rows -> huge data base with EAN (European Article Number).

When I click on the EAN column and try to search for e.g. 7000 codes, Qlik finds e.g. 3000 codes.
When I check e.g. 1 code that it didn't find for me, it magically turns out that it exists! When I paste those 7000 codes again, I suddenly get e.g. 6500 results.

Why doesn't Qlik find all the results right away?  I would like to point out that this error is random 😕

I don't have any filters, just a list of codes from Excel, the same every time.
On what basis does the search in Qlik work?

How can I be sure that Qlik will actually find all the data?

[marcus_sommer]

I'm not sure but I could imagine that the search-string is too long and hitting any limitation. At least in the elder QlikView releases there were such restrictions. If I remember correctly the usual workaround was to transfer the value-list to a variable and using it within selection-actions or a set analysis.

Beside this you may consider to group/categorize the EAN in any way before applying them as filter.

[Sebastian_Dec]

Thanks @marcus_sommer for your answer.

"If I remember correctly the usual workaround was to transfer the value-list to a variable and using it within selection-actions or a set analysis."
Can you elaborate on this part or provide links to some examples? I don't think I fully understand.

BTW: I see some post on the forum that the search bar has a 5000 character limit, but no one mentions a limit on the number of queries.

[marcus_sommer · Accepted solution]

I assume your value-list looks like:

(value1|value2|value3)

Instead of applying it directly as search against the field this list might be assigned to a variable in an input-box and then used within a selection-action or restricting the object-views per expressions with something like:

count({< EAN = {$(MyVar)}>} EAN)

[Sebastian_Dec · Accepted solution]

Ok @marcus_sommer , I listened to you and did this:

1. "Wpisz EAN" / "Enter EAN" -> here the user enters a list of EANs separated by a space
2. "Wyszukaj" / "Search" ->  button, searching for codes
3. "Czyść" / "Clear" ->  button removing the filter
4. "Wyszukanych EAN" / "Searched EANs" -> 
shows how many codes are found:

count({< kod_ean = {$(iCodesFormatted)}>} distinct kod_ean)

5. "Szukanych EAN" / "Found EANs" ->
shows how many codes were searched:

=SubStringCount(iCodesFormatted, chr(34))/2

The iCodesFormatted variable has the form of code:

=chr(34) & replace(concat(distinct SubField('$(iCodes)',' ',ValueLoop(1,$(=SubStringCount(iCodes,' '))+1)),' '), ' ', chr(34) & ',' & chr(34)) & chr(34)

iCodes is simply a list of codes from the "Enter EAN" field.

We are currently testing this solution

2025.01.11 EDIT: After 3 months of introducing filters, we have not had any reports of incompatibility. It turns out that sometimes Qlik does not catch the changes and you have to click the button again.