How DISTINCT and DROP FIELD work together on QlikView 12 and Qlik Sense

[Andrea_Bertazzo]

The script

Table1:
LOAD DISTINCT
      Inline [
      Field1, Field2, Field3
      1, 2, 1
      2, 2, 1
      3, 3, 1
      4, 3, 1];
DROP FIELD Table1.Field1;

creates a table with 4 rows.  Some developers, especially if used to work with QlikView 11, would expect a 2 rows table. In fact, after  Field1 is dropped, we can expect that the DISTINCT keeps only the distinct values from Field2.
This article explains how DISTINCT and DROP FIELD work in such a case.

Environment

• QlikView 12 all versions
• Qlik Sense on Windows all versions
• Qlik Cloud

Resolution

We can follow the script execution to understand why a 4 row table is the logic result.

1. The LOAD DISTINCT will create a table with 4 rows, because Field1 has no repetitions. All the values in the inline list will be imported. The table will look like this in memory:
   
   

   Field1	Field2	Field3
   1	2	1
   2	2	1
   3	3	1
   4	3	1

2. The DROP FIELD command is applied after the table creation. It removes Field1 from the memory. Notice that it does not force a re-apply of the DISTINCT, so the table will look like this in memory:
   
   

   Field2	Field3
   2	1
   2	1
   3	1
   3	1

   
   It is expected to have a table with 4 row and 2 repeated values even if the DISTINCT was applied in the LOAD statement. This occurs because the DROP FIELD occurred at the end. By dropping  Field1, we just removed that column from the memory, which means that Table1 will not have distinct rows anymore.

3. It is possible to use a script like this to have the distinct check in the second part of the script :

   tmp:
   LOAD DISTINCT
   Table1.Field2,Table1.Field3
   resident Table1;
   
   DROP Table Table1;
   
   Table1:
   NoConcatenate Load
   *
   Resident tmp;
   
   DROP table tmp;

Cause

Combining DISTINCT and DROP FIELD can lead to unexpected result. The behavior has changed with respect to QlikView 11.

Internal Investigation ID

QV-2384

[sebastianQlik]

If I add a simple LEFT JOIN onTable1 after the DROP Field command, I get a different result in QV 12.70 and 12.80. In 12.80 the data is multiplied.

Table1:
LOAD Distinct * INLINE [
Field1, Field2, Field3
1, 2, 1
2, 2, 1
3, 3, 1
4, 3, 1
];

DROP FIELD Field1;

LEFT JOIN
LOAD * INLINE [
Field2, Value
2, x, 1
2, a, 1
];

Result in 12.70 (see also in attachment):

Result 12.80 (see also in attachment):

Why does QV 12.80 show a different behaviour here? I have already written a Qlik Community article about this: https://community.qlik.com/t5/QlikView-App-Dev/Difference-between-12-70SR2-and-12-80SR1-when-using-q...