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

[Anonymous]

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.

[#2]

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

[Gysbert_Wassenaar]

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;

[Anonymous]

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?

[Gysbert_Wassenaar · Accepted solution]

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#];