Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello my QlikView friends. Will someone please explain to me what is wrong with this statement? Thank you
class_sub:
LOAD COUNT(DISTINCT AX_PO_NUM)>1;
LOAD 1 AS ROWCOUNT,
AX_PO_NUM,
AX_CLASS,
'MULTIPLE CLASSES' AS CLASS_DUP
RESIDENT sourcingclass_1;
Well through a lot of different trials and errors, I finally got the result that I was looking for. This is the code that worked for me. (Oddly enough, now Order By doesn't work like I would like for it to, but this is a minor inconvenience)...
sourcingclass_1:
LOAD DISTINCT tbl_sourcing.AX_PO_NUM AS AX_PO_NUM,
tbl_sourcing.AX_CLASS AS AX_CLASS
RESIDENT tbl_sourcing;
NoConcatenate
tmp_a:
LOAD AX_PO_NUM,
COUNT(AX_PO_NUM) AS TempKey
RESIDENT sourcingclass_1
GROUP BY AX_PO_NUM;
NoConcatenate
tmp_b:
LOAD AX_PO_NUM AS DUP_AX_PO_NUM
RESIDENT tmp_a
where TempKey > 1;
DROP TABLES tmp_a;
NoConcatenate
tmp_c:
LOAD DUP_AX_PO_NUM AS PO_NUMBER
RESIDENT tmp_b
ORDER BY DUP_AX_PO_NUM;
LEFT JOIN(tmp_c)
LOAD AX_PO_NUM AS PO_NUMBER,
AX_CLASS AS CLASS,
'MULTIPLE CLASS' AS CLASS_DUP
RESIDENT sourcingclass_1;
DROP TABLE tmp_b, sourcingclass_1;
Hi
What is the output you desire ?
You can't filter like this (I assume this was going to be a filter).
LOAD COUNT(DISTINCT AX_PO_NUM)>1;
Moreover GROUP BY is required for aggregate function like COUNT.
What are you trying to do?
The syntaxer does not return an error but you cannot use the where clause COUNT... You have to do it in some steps
class_sub:
LOAD 1 AS ROWCOUNT,
AX_PO_NUM,
AX_CLASS,
'MULTIPLE CLASSES' AS CLASS_DUP
RESIDENT sourcingclass_1;
LEFT JOIN (class_sub)
LOAD AX_PONUM,
COUNT(DISTINCT AX_PO_NUM) as COUNTER
RESIDENT class_sub
GROUP BY AX_PO_NUM;
class_sub_final:
Noconcatenate
LOAD *
RESIDENT class_sub where COUNTER >1;
drop table class_sub;
This is wrong:
COUNT(DISTINCT AX_PO_NUM)>1
I am trying to re-write this statement (from Access SQL) into QV:
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#];
It has not been easy.
then why don't you use an appropriate ODBC Access that will allow you to use you SQL statement in a QV load statement?
it is loading from a resident table created in QlikView. Here's the entire bit of code:
sourcingclass_1:
LOAD DISTINCT tbl_sourcing.AX_PO_NUM AS AX_PO_NUM,
tbl_sourcing.AX_CLASS AS AX_CLASS
RESIDENT tbl_sourcing;
NoConcatenate
class_sub:
LOAD COUNT(DISTINCT AX_PO_NUM)>1;
LOAD 1 AS ROWCOUNT,
AX_PO_NUM,
AX_CLASS,
'MULTIPLE CLASSES' AS CLASS_DUP
RESIDENT sourcingclass_1;
Maybe this:
class_sub:
LOAD
1 AS ROWCOUNT,
AX_PO_NUM,
AX_CLASS,
'MULTIPLE CLASSES' AS CLASS_DUP
RESIDENT sourcingclass_1;
tmp:
LOAD
AX_PO_NUM,
AX_CLASS,
CLASS_DUP
COUNT(DISTINCT AX_PO_NUM) as TempKey
RESIDENT class_sub
GROUP BY AX_PO_NUM, AX_CLASS, CLASS_DUP;
INNER JOIN (class_sub) LOAD
AX_PO_NUM,
AX_CLASS,
CLASS_DUP
RESIDENT tmp
WHERE TempKey>1;
DROP TABLE tmp;
Thank you for this code Michael. I am trying to wrap my brain around it. One thing I have noticed from the result set is that I am not getting a 1 for each record in rowcount. Any idea why that is?