Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Preceding Load not working properly

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;

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;

View solution in original post

11 Replies
Anonymous
Not applicable
Author

Hi

What is the output you desire ?

Not applicable
Author

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?

agilos_mla
Partner - Creator III
Partner - Creator III

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;

Anonymous
Not applicable
Author

This is wrong:

COUNT(DISTINCT AX_PO_NUM)>1

Anonymous
Not applicable
Author

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. 

agilos_mla
Partner - Creator III
Partner - Creator III

then why don't you use an appropriate ODBC Access that will allow you to use you SQL statement in a QV load statement?

Anonymous
Not applicable
Author

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;

Anonymous
Not applicable
Author

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;

Anonymous
Not applicable
Author

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?