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.
- 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 |
- 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.
-
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
