Count Invoice Lines with Count over Partition by in Qlikview?

[#1]

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

[sujeetsingh]

hi,

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

[#3]

Hi,

Would you please be more specific please? Any example?

[Anonymous]

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

[Anonymous · Accepted solution]

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

[#6]

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

[#7]

Hi Jonathan,

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

[#8]

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

[#9]

I check with test data, just a moment.

[#10]

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