Rows disappear in stacked load containing Applymap()

[K_Wilde]

Dear All,

recently we experience issues connected to the ApplyMap() function.

We have only recently updated our software to QlikvView May 2024 (yes we are late...).

This issue only seems to occur when working with large datasets. 

We are using the ApplyMap function in many places in  multiple apps and it has always worked as expected:

When no value from the mapping table can be mapped to the table, either the expression itself was returned, or the default_mapping, if defined.

But now it happens that when there is no value in the mapping table, the whole row of the table is lost.

We can work around that by including additional steps in the stacked load but we would need to change a lot of applications.

Has anybody experienced the same issue? Is there a quick, sort of central solution? Has this been identified a bug in the QV version?

Best,
Karin

[marcus_sommer]

Applymap() is a quite old feature and AFAIK unchanged since ages - therefore I doubt that there is a new bug.

More likely seems to be that the mapping-results and/or other stuff are used in explicit/implicit filters and removing the records or just preventing the view of these information in regard to certain object-dimensions or selection states.

Don't try to look for these data with any charts + measures else using a table-box with some relevant fields and appropriate recno() + rowno() information. If the records are really gone then go step by step through the ETL chain to detect in which place it happens and if there are any filters applied. It might be completely unrelated to the mapping - just caused from differences anywhere within the environment (regional-settings, interpretation-variables, a lot of possibilities ...).

Beside the above an applymap() should always get a default-value for a non-matching - it might be an empty string or an explicit null() or anything else.

[K_Wilde]

Thank you for your reply, Markus. 
It really seems to be connected to the mapping in some sort. I'm mapping multiple fields and when I leave out the mapping for the field where no value can be found in the mapping table, the row is not lost. 

What I did not mention before: We are using the mapping along with a keep() based on the table the mapping tables are created from, according to the following pattern.

Table_1:

Load ID,

         XY

From Table_1.qvd(qvd);

Map_XY:

Load ID,

      XY

Resident Table_1;

Left keep(Table_1): //keep on ID

Load *,

ApplyMap(‚Map_XY‘, ID) as XY;

Load ID_2,

        ID,

        Amount

From table_2.qvd (qvd);

Drop Table Table_1;

The behaviour in this kind of pattern seems changed recently.

[marcus_sommer · Accepted solution]

The left keep is an implicit filter - keeping only those records which have equally values within the key-fields.

Maybe there is were changes to the data-set and/or the data-quality which are now leading to this non-matching.

[K_Wilde · Accepted solution]

That's probably the reason. Thank you for thinking with me!

I found a work around: When you rename the fields in the source table for the mapping table in the first place, the records are not lost. The pattern is than like this:

Table_1:

Load ID,

         XY as XY_2

From Table_1.qvd(qvd);

Map_XY:

Mapping Load ID,

      XY_2

Resident Table_1;

Left keep(Table_1): //keep on ID

Load *,

ApplyMap(‚Map_XY‘, ID) as XY;

Load ID_2,

        ID,

        Amount

From table_2.qvd (qvd);

Drop Table Table_1;