topic Re: Count Invoice Lines with Count over Partition by in Qlikview? in QlikView

Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 11:24:16 GMT

Hi,

I have a report in Qlikview that shows Invoice numbers and Invoice Lines (besides other data) and I would like to have a column that counts the number of Invoices Lines contained in an Invoice. Basically, I would like to build a table like this:

Invoice #	Invoice Line	Num_lines
1	a	3
1	b	3
1	c	3
2	a	2
2	b	2
3	a	4
3	b	4
3	c	4
3	d	4

If I were to do that with a sql statement my query would look like this:

SELECT Inv_num, Inv_line, COUNT(*) OVER(PARTITION BY Inv_num) as Num_lines FROM TABLE1

Is there a way to get something similar in Qlikview?

Kind Regards

Julian

Re: Count Invoice Lines with Count over Partition by in Qlikview?

sujeetsingh — Wed, 16 Jan 2013 11:28:58 GMT

hi,

you can try it with loading and then group by ...

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 11:30:36 GMT

Hi,

Would you please be more specific please? Any example?

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Anonymous — Wed, 16 Jan 2013 11:30:58 GMT

You can reload the table using a group by Inv_num, and include a field that is either a count of Inv_num or a key, or a sum of a flag '1' created on the first load.

Jonathan

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Anonymous — Wed, 16 Jan 2013 11:36:28 GMT

First load could be ....

InvTable:

LOAD

Inv_num,

1 AS CountFlag

... etc

FROM Invoices.xls....

Second load could then be ....

LEFT JOIN (InvTable) LOAD

Inv_num

sum(CountFlag) AS CountOfRows

RESIDENT InvTable

GROUP BY Inv_num;

Jonathan

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 12:00:23 GMT

Hi Julian,

do you need it in the script?

If not, you could use set analysis in an expression:

=Count({1<inv_num={$(=inv_num)}>} Inv_line)

Ciao

Klaus

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 13:54:53 GMT

Hi Jonathan,

Yes! Your answer works like a charm! Thanks a lot!

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 13:57:58 GMT

Hi Klaus,

Thanks for your reply and your solution.I actually like the idea of using set analysis, but unfortunatelly I could not make it work. It launches an "error in calculation"

Regards

Julian

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 14:03:41 GMT

I check with test data, just a moment.

Re: Count Invoice Lines with Count over Partition by in Qlikview?

Wed, 16 Jan 2013 15:00:09 GMT

Sorry, can't get that one working - even with aggr.