topic Re: Countifs with column value match in App Development

Countifs with column value match

ZimaBlue — Fri, 28 Jan 2022 13:58:40 GMT

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.

Re: Countifs with column value match

rubenmarin — Sat, 29 Jan 2022 08:32:12 GMT

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

Re: Countifs with column value match

ZimaBlue — Sat, 29 Jan 2022 12:34:20 GMT

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?

Re: Countifs with column value match

ZimaBlue — Sat, 29 Jan 2022 12:53:42 GMT

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.

Re: Countifs with column value match

rubenmarin — Sat, 29 Jan 2022 18:25:55 GMT

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;

Re: Countifs with column value match

ZimaBlue — Sun, 30 Jan 2022 01:26:16 GMT

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

Thanks again for your help!