topic Re: count strongest line in QlikView

count strongest line

orh12345 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,
I am new in QlikView
I have a table that contains dates, campaigns and responses.
I have to take the strong responses according to selected dates and the pre-adjusted hierarchy
The hierarchy From strong to weak: Positive, Negative, Not answered

dates, campaigns ,responses
01.01.2019, 1, Positive
02.01.2020 , 1, Negative
03.03.2020, 1, Negative

01.12.2019, 2, Not answered
02.01.2020 , 2, Not answered
03.03.2020, 2, Negative


01.01.2019	1	Positive
02.01.2020	1	Negative
03.03.2020	1	Negative
01.12.2019	2	Not answered
02.01.2020	2	Not answered
03.03.2020	2	Negative

The result I expect to see:

year	Positive	Negative	Not answered
2019	1	0	1
2020	0	2	0

Re: count strongest line

Taoufiq_Zarra — Mon, 07 Sep 2020 10:03:13 GMT

Hi @orh12345

One solution

Data: LOAD *,Year(dates) as year INLINE [ dates,campaigns,responses 01.01.2019, 1, Positive 02.01.2020, 1, Negative 03.03.2020, 1, Negative 01.12.2019, 2, Not answered 02.01.2020, 2, Not answered 03.03.2020, 2, Negative ]; Tmp: load year, if(responses='Positive',count(distinct campaigns),0) as Positive,if(responses='Negative',count(distinct campaigns),0) as Negative ,if(responses='Not answered',count(distinct campaigns),0) as [Not answered] resident Data group by year,responses; drop table Data;

in Interface :

Dimension: Year

Measures: sum(Positive)->Positive

sum(Negative)->Negative

sum(Not answered)->Not answered

Inline .... to change with from ... your database or file ...

Re: count strongest line

orh12345 — Mon, 07 Sep 2020 09:51:51 GMT

Hi,
thank you for the answer,
The required solution should be in the UI after loading.
Plus, your output is not like mine, I need to get in negative = 2
In my question I added the expected output

Re: count strongest line

Taoufiq_Zarra — Mon, 07 Sep 2020 10:01:27 GMT

in this case :

Dimension : =year(dates)

Measures:

=count({<responses={'Positive'}>} distinct campaigns) for Positive

=count({<responses={'Negative'}>} distinct campaigns) for negative

=count({<responses={'Not answered'}>} distinct campaigns) for not answered

output :

@orh12345

Re: count strongest line

orh12345 — Mon, 07 Sep 2020 11:04:00 GMT

Hi
thank you for the answer,
Unfortunately again your output is not like mine,
Your output ignores the strong response requirement.
For example, in 2020, Campaign 2 was counted twice, once unanswered and once in negative,
The output should take into account that there is a hierarchy of responses, the strong response to Campaign 2 in 2020 is negative and therefore should be counted once in the negative and that is it.