Count Number of Rows in distinct sums

[louisernould]

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

[demoustier]

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;

[#3]

Go to a pivot table

Dimension-Command

Expression1-sum(Quantity)

Expression2-noofrows(Command)

[adhudson]

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 ! !

[#5]

Or Expression2-Count(Command)

[#6]

or Expression2-count(Command)

[louisernould]

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 !

[#8]

This is the easiest way to count rows..

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

[louisernould]

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

[demoustier]

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>

[#11]

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..