topic Re: Count with Group-by function in script in QlikView

Count with Group-by function in script

MartinMCS — Sat, 25 Feb 2023 13:55:01 GMT

Hi Qlik community,

I'm new Qlik product I'm facing a issue any one helps

I have a sample data which is attached picture here, i need to count with group-by, for this i wrote like this:

Table1:

Load Prod, Year & '-' & Month as KEY, Price; Load Prod, Year, Month, Price from Table1;

Table2:

Load Ord. No, Year & '-' & Month as KEY, Id; Load Ord. No, Year, Month, Id from Table2;

Result_Table:

Load Id, Count (Id) as Count_Id Resident Table2

Group By Id;

But it's not met my requirement, what should i do to achieve expected result from script and where i'm wrong? and one more question, for this row -level set analysis will works, if yes, what is syntax?

Any help wuold be appreciated.

Thanks in advance.

Re: Count with Group-by function in script

henrikalmen — Sat, 25 Feb 2023 17:35:37 GMT

What is your requirement / expected result?
And I’m not sure what your second question is, could you clarify?

Re: Count with Group-by function in script

MartinMCS — Sun, 26 Feb 2023 02:17:22 GMT

Hi, need to create a new table in script with columns of Couunt(Id) as Count_Id and Id. If I select Prod is 'Y2021' the corresponding field Count_Id is should show '2'.

Second question is set analysis or expression works for this? If yes, how to write set expression to achieve Average in front-end.

Re: Count with Group-by function in script

henrikalmen — Sun, 26 Feb 2023 07:00:33 GMT

Load Id, Count (Id) as Count_Id Resident Table2 Group By Id;

That statement does what it should. It gives you a table with Id and the Count_Id as you want.

But those values are static. If you need it to show different values when doing selections in the Prod field, you probably need to create your table like this:

Load Id, Prod, Count (Id) as Count_Id Resident Table2 Group By Id, Prod;

But maybe you shouldn’t create such a table in backend at all. Try creating a table chart with Id as dimension, and count(Id) as measure. You don’t need to use “group by” in frontend.

I'm still not sure about your second question. Generally, if you want to calculate average you use the avg() function instead of sum().