topic More fun with WHERE.... IN.... in QlikView

More fun with WHERE.... IN....

Anonymous — Wed, 22 Jan 2014 14:10:39 GMT

Hello fellow QlikView enthusiasts. I have a SQL statement that I am really struggling with. Would someone out there please re-write this exactly how it should be?

SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup
FROM sourcingclass_1
WHERE (((sourcingclass_1.[AX PO#]) In (SELECT [AX PO#] FROM [sourcingclass_1] As Tmp GROUP BY [AX PO#] HAVING Count(*)>1 )))
ORDER BY sourcingclass_1.[AX PO#];

I think I should be writing this as a WHERE EXISTS, but would really appreciate a clearly written final answer for myself and others as a reference. Thank you.

Re: More fun with WHERE.... IN....

Wed, 22 Jan 2014 17:08:17 GMT

You can try below queries:

SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup

FROM sourcingclass_1

join (SELECT [AX PO#] FROM sourcingclass_1 GROUP BY [AX PO#] HAVING Count(*)>1) test on sourcingclass_1.[AX PO#] = test.[AX PO#]

ORDER BY sourcingclass_1.[AX PO#];

or you can use below

SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup

FROM sourcingclass_1

WHERE sourcingclass_1.[AX PO#] In (SELECT [AX PO#] FROM sourcingclass_1 GROUP BY [AX PO#] HAVING Count(*)>1)

ORDER BY sourcingclass_1.[AX PO#];

The first one will be better since we are using join clause which is more performance oriented than Where clause.

Thanks,

Angad

Re: More fun with WHERE.... IN....

Gysbert_Wassenaar — Wed, 22 Jan 2014 17:55:42 GMT

You can simply use your sql statement to load the data you want into qlikview. Qlikview will simply send the sql statement to the dbms (or oledb/odbc driver) for execution and will load the records that are returned.

You could also try this, which will be less efficient:

Temp:

LOAD [AX PO#], if(exists([AX PO#]), [AX PO#]) as [AX PO#_2];

SELECT [AX PO#] FROM sourcingclass_1;

Result:

LOAD * WHERE exists([AX PO#_2],sourcingclass_1.[AX PO#]);

SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup FROM sourcingclass_1;

DROP table Temp;

Re: More fun with WHERE.... IN....

Anonymous — Wed, 22 Jan 2014 19:00:04 GMT

Gysbert, my data is already in QlikView, so a SELECT statement will not work. Any ideas on how to write this for data already in QlikView?

Re: More fun with WHERE.... IN....

Gysbert_Wassenaar — Wed, 22 Jan 2014 20:50:47 GMT

Ok, assuming you already loaded the data into a table called sourcingclass and want a new table with only the [AX PO#] field values that have more than one related record in sourcingclass:

Result:

load [AX PO#] where Count >1;

load [AX PO#], count([AX PO#]) as Count

resident sourcingclass

group by [AX PO#];