Countifs with column value match

[ZimaBlue]

Hello there!

Here in the community I managed to find a replacement for countifs from google spreadsheets for qlik. But I couldn't figure out how to use this not for a specific value, but for a column.

What I need: count(distinct Column_1 where value for Column_3 is like in this row).

That is, if we look at the table below, I want each row in the Column_count column to have the number of unique values from Column_1 for which Column_3 = banana (for row 1); apple (for row 2) and so on. 

-> If I have 12 stores that sell bananas, I want to see 12 in row 1, and if I have 20 stores that sell apples, I want to see 20 in row 2

Column_1	Column_2	Column_3	Column_count
store_1	pa ra pa pa pam	banana	12
store_2	pa ra pa pa pam	apple	20

This is a simplified example, in fact I need to list 2 or 3 columns in the filter.

[rubenmarin]

Hi, you can try with: Count(Distinct TOTAL <Column_3> Column_1)

TOTAL tells to ignore dimensions, and the field between <> is the field(s) to not ignore

[ZimaBlue]

Hello! Thank you for your reply!

It seems that total is intended only for visualization, not for the script editor 😞 since I get an error when loading data. And by syntax highlighting, I also see that the script does not recognize TOTAL as a command.

Is there a way to write this in a data loading script?

[ZimaBlue]

However, I tested it for visualization and it works brilliantly - thank you for the help!

I would still like to know if it can be used right inside the script.

[rubenmarin · Accepted solution]

Hi, TOTAL is used to ignore dimensions in chart, and in script there are no charts. A similar utility would be the 'group by'

In example if you have a 'Data' table with columns 1, 2 and 3, you can add the new colum in the next step with just:

Left Join(Data)
Load Column_3,
  Count(Distinct Column_1) as Column_Count
Resident Data
Group by Column_3;

[ZimaBlue]

I understand that now. It turned out to be easier, I made it too complicated. 😃

Thanks again for your help!