topic Re: Count the outcome by comparing 3 column in App Development

Count the outcome by comparing 3 column

QlikSenseBeginner — Sat, 16 Nov 2024 16:35:24 GMT

Hi Qlik Community,

Need your help with the Set Analysis.

The following is a sample excel data.

INTERNSHIP COMPANY CLEANED	TOP EMPLOYERS CLEANED	Were you offered a position by the company you were attached to for your internship or work attachment
A	A	Yes. I am currently working in the company that I was attached to
	B	Yes. I am currently working in the company that I was attached to
	C	Yes. I am currently working in the company that I was attached to
	D	Yes. I am currently working in the company that I was attached to
EE	EE	No. I was not offered a position and my internship(s) / work attachment(s) / work study scheme(s) did not help me obtain a job in other ways
FF	F	No. I was not offered a position, but the internship(s) / work attachment(s) / work study scheme(s) has helped me obtain a job in other ways
GGG	GGG	Yes. I am currently working in the company that I was attached to

I would like to count the number of cases where column 1 = column 2 = column 3 'Yes. I am currently working in the company that I was attached to'. Which i can tell from the table that the count should be 2.

What would be the set analysis that i should have? I have a slight idea what i wanted but i need some help.

count({$<[INTERNSHIP COMPANY CLEANED]={[XXX]},TOP EMPLOYERS CLEANED={[XXX]},[OFFERED POSITION]={[Yes. I am currently working in the company that I was attached to]}>} [Were you offered a position by the company you were attached to for your internship or work attachment])

Matric is a unique ID.

Can the XXX be left as wild card?

Re: Count the outcome by comparing 3 column

Kushal_Chawda — Wed, 02 Oct 2024 08:34:17 GMT

@QlikSenseBeginner to use set analysis, you will need PrimaryKey in your table. If you don't have you can create it using RowNo() as use below set analysis

Data:
Load *,
RowNo() as Row
FROM table;

Where Row is primary key of the table. If you already have it, you can utilise it.

=Count({<Row= {"=[INTERNSHIP COMPANY CLEANED]=[TOP EMPLOYERS CLEANED]"}, [OFFERED POSITION]={'YES'}>}Metrics)

If you don't have primary key and you don't want to create it using load script, you can use if condition expression as below

=count(if([INTERNSHIP COMPANY CLEANED]=[TOP EMPLOYERS CLEANED] and [OFFERED POSITION]='YES', Row))

Re: Count the outcome by comparing 3 column

QlikSenseBeginner — Wed, 02 Oct 2024 08:40:42 GMT

This part seems interesting

count(if([INTERNSHIP COMPANY CLEANED]=[TOP EMPLOYERS CLEANED] and [OFFERED POSITION]='YES', Row))

If i do not need the ROW is there another way to write this?

Re: Count the outcome by comparing 3 column

Kushal_Chawda — Wed, 02 Oct 2024 08:44:22 GMT

@QlikSenseBeginner Yes with if you don't need Row as it will perform row by row operation.

count(if([INTERNSHIP COMPANY CLEANED]=[TOP EMPLOYERS CLEANED] and [OFFERED POSITION]='YES', Merics))

Re: Count the outcome by comparing 3 column

QlikSenseBeginner — Wed, 02 Oct 2024 08:56:18 GMT

Thanks, it says error.

IF takes 2-3 parameters.

count(if([INTERNSHIP COMPANY CLEANED]=[TOP EMPLOYERS CLEANED] and [OFFERED POSITION]='YES', Matric))

Re: Count the outcome by comparing 3 column

Kushal_Chawda — Wed, 02 Oct 2024 08:57:35 GMT

@QlikSenseBeginner Id your Meric is other expression? you need to use the field which you want to count.

Re: Count the outcome by comparing 3 column

QlikSenseBeginner — Thu, 03 Oct 2024 07:22:28 GMT

I have revised the table a little. Hopefully it brings some light to the reader. Only when the following met it will be counted as 1

INTERNSHIP COMPANY CLEANED

TOP EMPLOYERS CLEANED

Were you offered a position by the company you were attached to for your internship or work attachment

Yes. I am currently working in the company that I was attached to

Re: Count the outcome by comparing 3 column

Kushal_Chawda — Thu, 03 Oct 2024 08:29:33 GMT

@QlikSenseBeginner try below

count(if([INTERNSHIP COMPANY CLEANED]=[TOP EMPLOYERS CLEANED] and [OFFERED POSITION] like 'Yes*',[INTERNSHIP COMPANY CLEANED]))