Solved: Re: Count Number of Rows in distinct sums - Qlik Community

louisernould · ‎2014-09-22

Hello dear Qlikview's masters,

I got a problem, and I'm sure you could have the solution.

I am calculating (from a big excel file (around 1000000 rows)) a lot of sums.

But, I want to know how many rows I have in my sum (this is my problem )

for example:

Command	Quantity
A	3
A	2
A	3
B	4
B	3
B	2
B	6

I have : if Command='A' so, sum(Quantity)=8 (3+2+3)), and I want the number of rows (3)

and if Command='B' so, sum(Quantity)=15 (4+3+2+6)), and I want 4

So I think I want a table like this

Command	Sum	Number of rows
A	8	3
B	15	4

Does someone have an idea ? In the script or in an expression,...

Thanks in advance

louisernould · ‎2014-09-23

I don't find Desmoustier's answer, so I put it here again:

TMP_Réalisé:

LOAD Site,

Ref,

Réalisé

1 as row_count

FROM [File1];

INNER JOIN (TMP_Réalisé)

LOAD @1 as NumSem,

@2 as Annee,

@3 as [PRE Date réalisé]

FROM [File2];

TMP_Date:

LOAD Distinct([PRE Date réalisé]+56) as DatePourRéalisé

Resident TMP_Réalisé;

LET NbDates = NoOfRows('TMP_Date');

FOR i=1 to $(NbDates)

LET DateChargement = FieldValue('DatePourRéalisé',$(i));

TableRéalisé:

LOAD $(DateChargement) as [Date réalisé],

Site,

Ref,

Sum(Réalisé) as QuantitéRéalisé,

Sum(row_count) as NumberOfRows

Resident TMP_Réalisé

Group by Ref, Site;

DROP Table TMP_Réalisé;

View solution in original post

anbu1984 · ‎2014-09-22

Load Command,sum(Quantity),count(Command) group by Command;

Load * Inline [

Command,Quantity

A,3

A,2

A,3

B,4

B,3

B,2

B,6 ];

louisernould · ‎2014-09-22

Hi Anbu,

Actually I already try this solution but I have too many rows and too many commands (something like 200000...) So I cannot do it... Or I can start today and I will finish it in 2 weeks

maybe more in an expression ?

JonnyPoole · ‎2014-09-22

A Straight Table with Command as the dimension, sum(Quantity) as the 1st expression and count(Command) as the 2nd expression could work. This will count the # rows from the data model for each unique command.

If you have a unique record identifier in the data model you could also do count(distinct [UniqueIdentifierFieldName] )

louisernould · ‎2014-09-22

It doesn't work...

My mistake, I forgot something: The 'sum' is already in the script

LOAD

Command,

Sum(Quantity) as sum

from ...

So maybe the Number of row of the sum has to be in the script... I'm a little bit confused...

The Command is not so important, I just want to know if it is possible to count the number of rows of a sum.

anbu1984 · ‎2014-09-22

No it is not possible to count the number of rows of a sum

louisernould · ‎2014-09-22

Oh no...

even with a loop ? like:

for each sum,

count (rowno())

JonnyPoole · ‎2014-09-22

To get the number of rows that were used to create a sum, you need to do a count them in the load script using the 1st suggestion from Anbu above.

1,000,000 records is really not very much so i don't understand the performance issues even if your laptop is small.

How long does it take to run a load script with the sum() function and group by ?

anbu1984 · ‎2014-09-22

No. Why can't you calculate count along with sum?

Anonymous · ‎2014-09-22

Hi Louis,

In Below table For A data you want to see in 3 rows for that there should be some other column which we can use to do grouping.

Command	Quantity
B	4
B	3
B	2
B	6
A	3
A	2
A	3

Can you share your sample .qvw file.

Thanks

Manish