topic Re: distinct data in QlikView

distinct data

pakdoust — Sat, 16 Nov 2024 02:04:15 GMT

With the script below I have a result of 4 rows including PID '123' two times (with 'ab' and 'abcdef').

Now I'd like to keep only one row if it has the same PID : in this cas I'd like to keep '123', 'abcdef' instead of '123', 'ab'

Table1:
LOAD * Inline [
PID , brand
123 , ab
120, ab
];

NoConcatenate
Table2:
LOAD * Inline [
PID , brand
123 , abcdef
145 , ab
];

Concatenate
Load distinct *
Resident Table1;

Result wanted :

120, ab

123, abcdef

145, ab

Thanks

Re: distinct data

JGMDataAnalysis — Thu, 31 Oct 2019 14:42:28 GMT

Try with this...

Table2: LOAD * INLINE [ PID, brand 123, abcdef 145, ab ]; Table1: LOAD * INLINE [ PID, brand 123, ab 120, ab ] WHERE Not Exists(PID);

Re: distinct data

pakdoust — Thu, 31 Oct 2019 14:58:48 GMT

Is it possible to get the same result without changing the order of table LOADS ?

Re: distinct data

JGMDataAnalysis — Thu, 31 Oct 2019 15:22:43 GMT

Table1: LOAD * INLINE [ PID, brand 123, ab 120, ab ]; Table2: NOCONCATENATE LOAD *, PID AS tmpPID INLINE [ PID, brand 123, abcdef 145, ab ] ; CONCATENATE(Table2) LOAD * RESIDENT Table1 WHERE Not Exists(tmpPID, PID); DROP TABLE Table1; DROP FIELD tmpPID;