topic Re: Count Number of Rows in distinct sums in QlikView

Count Number of Rows in distinct sums

louisernould — Mon, 22 Sep 2014 12:33:33 GMT

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

Re: Count Number of Rows in distinct sums

anbu1984 — Mon, 22 Sep 2014 12:36:33 GMT

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 ];

Re: Count Number of Rows in distinct sums

louisernould — Mon, 22 Sep 2014 12:39:44 GMT

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 ?

Re: Count Number of Rows in distinct sums

JonnyPoole — Mon, 22 Sep 2014 12:43:38 GMT

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] )

Re: Count Number of Rows in distinct sums

louisernould — Mon, 22 Sep 2014 12:57:04 GMT

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.

Re: Count Number of Rows in distinct sums

anbu1984 — Mon, 22 Sep 2014 12:59:54 GMT

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

Re: Count Number of Rows in distinct sums

louisernould — Mon, 22 Sep 2014 13:07:28 GMT

Oh no...

even with a loop ? like:

for each sum,

count (rowno())

or something like that ?

Re: Count Number of Rows in distinct sums

JonnyPoole — Mon, 22 Sep 2014 13:14:34 GMT

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 ?

Re: Count Number of Rows in distinct sums

anbu1984 — Mon, 22 Sep 2014 13:22:31 GMT

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

Re: Count Number of Rows in distinct sums

Anonymous — Mon, 22 Sep 2014 13:35:55 GMT

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

Re: Count Number of Rows in distinct sums

demoustier — Mon, 22 Sep 2014 13:36:53 GMT

Try this in your script:

// loading your data + row counter ('1' entered in each line) in a temporary table

tmp:

LOAD Command,

Qty,

1 as row_count

FROM

C:\Users\......\Classeur1.xlsx

(ooxml, embedded labels, table is Feuil1);

// loading from the temporary table the sum of quantity and the sum of row, grouped by command

NoConcatenate

load

Command,

sum(Qty) as Sum,

sum(row_count) as Row_num

Resident tmp group by Command;

//delete temporary table

drop table tmp;

Re: Count Number of Rows in distinct sums

Mon, 22 Sep 2014 14:15:08 GMT

Go to a pivot table

Dimension-Command

Expression1-sum(Quantity)

Expression2-noofrows(Command)

Re: Re: Count Number of Rows in distinct sums

adhudson — Mon, 22 Sep 2014 14:21:41 GMT

Hi,

We do not have such syntax in QlikView to calculate sum.

But we have two options here to solve your "problem".

1. Using Count() to calculate number of rows.

2. Using the combination of AutoNumber() and RecNo() functions.

I have done both the approaches in the attached QVW file.

Regards,

Andrew Hudson ! !

Re: Count Number of Rows in distinct sums

Mon, 22 Sep 2014 14:27:59 GMT

Or Expression2-Count(Command)

Re: Count Number of Rows in distinct sums

Mon, 22 Sep 2014 14:28:28 GMT

or Expression2-count(Command)

Re: Count Number of Rows in distinct sums

louisernould — Mon, 22 Sep 2014 14:44:17 GMT

wow, so much answers !

I really want to share my qvw file but it's a private one, so I cannot share it , I am not allowed...

I tried to explain it easier but I think I failed ^^ and I tried everything you gave to me, but it doesn't work either.

Here is my full code, Maybe you will see some newbee mistakes,

TMP_Réalisé:

LOAD Site,

Ref,

Réalisé

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é

Resident TMP_Réalisé

Group by Ref, Site;

NEXT

DROP Table TMP_Date;

DROP Table TMP_Réalisé;

I don't know if it's enough or not... But the fact is, the Count Function doesn't work for my pb: I want to count the total of rows in the 'sum(Réalisé)'

If you don't know how to do it, don't worry, I will drop it

but thanks for your time everybody !

Re: Count Number of Rows in distinct sums

Mon, 22 Sep 2014 14:52:03 GMT

This is the easiest way to count rows..

Louis add that field "1 as row_count" in the script, and then sum it.

Re: Count Number of Rows in distinct sums

louisernould — Mon, 22 Sep 2014 14:58:39 GMT

I just did the row_count trick,

here is a selection, but when I sum it, I have only '1', and in my excel file, I have 6 rows

Re: Count Number of Rows in distinct sums

demoustier — Mon, 22 Sep 2014 15:08:51 GMT

Vu le libellé des colonnes, je passe au français ☺

Si tu as une commande par Date réalisé, c’est normale d’avoir 1 à chaque fois

Si tu as plusieurs commandes par Date réalisé il faut faire un group by Date réalisé.

Tu ne peux pas envoyer un échantillon de ton fichier avec des données fictives et aléatoires ?

Benjamin DEMOUSTIER,

De : louis ernould

Envoyé : lundi 22 septembre 2014 16:59

À : DEMOUSTIER Benjamin

Objet : Re: - Count Number of Rows in distinct sums

Qlik Community<http://community.qlik.com/>

Count Number of Rows in distinct sums

reply from louis ernould<http://community.qlik.com/people/louisernould?et=watches.email.thread> in New to QlikView - View the full discussion<http://community.qlik.com/message/615625?et=watches.email.thread#615625>

Re: Count Number of Rows in distinct sums

Mon, 22 Sep 2014 15:12:35 GMT

Mmmmmm, that's not possible. It should work.

Maybe you are using the sum in the script, you must use it in the chart or table, so it will be filtered by "command" using Set Analysis.

Or you could use autonumber as row_count, and then count them instead of sum them. Anyway, whichever you choose, the sum/count its in a expression, not in the script.

Hope that's the answer..