Dynamic 'CountIf' in QlikSense Load Script

[Qnoob]

Hi There,

I am new to Qlik Sense and trying to create simple excel calculations in Qlik Sense.

I have the below table.

The yellow highlighted column is a calculated column which is returning the total count of projects in column B for each row. This is achieved using a simple 'COUNTIF' function in Excel.

How can this be replicated in QS Load Script?

I know we can use 'COUNT(IF(MATCH()))' if the 'value to be matched'  can be hard coded. However I need to keep that dynamic, that is the 'value to be matched' should be the value in field 'Project' for each records.

Could you please help? 

Qlik Sense Business @wade12 

[Oliver_F · Accepted solution]

Hi,

if I get your request right, this will do what you are looking for:

1) load a new table "temp" resident from the existing table with just the field "project" and the new field count(project) and group it by project.

====

temp:
load
Project,
count(Project) as CountOfProject
Resident yourTable
group by Project;

====

2) make a left join to your existing table:

left join(yourTable)

load * resident temp;

drop table temp;

is this what you are looking for?

[Mark_Little]

Hi,

Don't have time to test it at the moment but you are going to want to look at the total function. Then using a set expression to make sure it is by project. Something like

COUNT(TOTAL<Project> Project)

[Qnoob]

Thanks Oliver, I did exactly the same (instead of Join, I did an 'Apply Map') and it did the trick. Thanks for the logic, I really need to get out of 'the excel way' of data modeling 🙂

[Qnoob]

Oh this works too!! Thanks Mark!!