topic Re: Preceding Load not working properly in QlikView

Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 19:06:33 GMT

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;

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 19:09:34 GMT

What is the output you desire ?

Re: Preceding Load not working properly

Tue, 04 Feb 2014 19:11:57 GMT

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?

Re: Preceding Load not working properly

agilos_mla — Tue, 04 Feb 2014 19:15:03 GMT

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;

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 19:16:12 GMT

This is wrong:

COUNT(DISTINCT AX_PO_NUM)>1

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 19:17:25 GMT

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.

Re: Preceding Load not working properly

agilos_mla — Tue, 04 Feb 2014 19:21:07 GMT

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

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 19:23:11 GMT

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;

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 19:37:47 GMT

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;

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 20:17:47 GMT

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?

Re: Preceding Load not working properly

Anonymous — Tue, 04 Feb 2014 20:34:05 GMT

Can't tell... Maybe there is something outside this part. I hope you'll find out and tell us.

Re: Preceding Load not working properly

Anonymous — Thu, 06 Feb 2014 16:19:30 GMT

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;