Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
thnkgreen
Contributor

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
thnkgreen
Contributor

Re: Preceding Load not working properly

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;

11 Replies

Re: Preceding Load not working properly

Hi

What is the output you desire ?

Not applicable

Re: Preceding Load not working properly

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
Contributor III

Re: Preceding Load not working properly

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;

mov
Esteemed Contributor III

Re: Preceding Load not working properly

This is wrong:

COUNT(DISTINCT AX_PO_NUM)>1

thnkgreen
Contributor

Re: Preceding Load not working properly

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
Contributor III

Re: Preceding Load not working properly

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

thnkgreen
Contributor

Re: Preceding Load not working properly

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;

mov
Esteemed Contributor III

Re: Preceding Load not working properly

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;

thnkgreen
Contributor

Re: Preceding Load not working properly

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?

Community Browser