topic Re: how to achive both columns data we have in some other column data using filter. in QlikView

how to achive both columns data we have in some other column data using filter.

satheshreddy — Wed, 11 Oct 2017 11:57:29 GMT

Hi Experts,

I have below data..

Company1	Company2	Company3
A1	D1	A1
B1	E1	D1
C1		B1

here my filters like below.

Filter

NON A1

once I click A1I need to see Company1 column data and when I click NON A1 I need to see Company2 data.

when am going to select NON A1 and A1 I need to see what ever we have both columns data in company3 column data.

How we can Achieve this

Regards

Sathish

Re: how to achive both columns data we have in some other column data using filter.

mdmukramali — Wed, 11 Oct 2017 12:02:42 GMT

Dear,

i think you can use cross table concept.

can you share sample data file.

Thanks,

Mohammed Mukram

Re: how to achive both columns data we have in some other column data using filter.

Anil_Babu_Samineni — Wed, 11 Oct 2017 12:07:29 GMT

Can you attach sample to look where Column 1/2/3 coming from?

Re: how to achive both columns data we have in some other column data using filter.

satheshreddy — Thu, 12 Oct 2017 06:19:20 GMT

Hi Anil,

as of now I don't have Qlikview software

I will explain one more way

I have policy types like below

fire, accident.

here am creating one filter like

filter

fire

accident

when I click fire I need to see how many company's are have fire policy that company's count I need

same way when we click accident I need to see how many company's have accident policy.

here one query

normally when we select both we will get hole data.

but I need like below

when I select both I need count (company's) what ever we have both Policy's company's.

Regards

Sathish

Re: how to achive both columns data we have in some other column data using filter.

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:23:17 GMT

Qlikview is a Advanced Associative Engine. That means, It can do automatic when we use simply Count(Measure)

Re: how to achive both columns data we have in some other column data using filter.

tresB — Thu, 12 Oct 2017 06:27:15 GMT

The :AND-Mode in listbox could be useful here. Otherway, perhaps could be using set analysis.

Re: how to achive both columns data we have in some other column data using filter.

satheshreddy — Thu, 12 Oct 2017 06:29:33 GMT

Hi Anil,

Data like below

sno	company	policy
1	google	fire
2	accenture	accident
3	ibm	accident
4	wipro	accident
5	infosys	fire
6	hcl	fire
7	dell	fire
8	dell	accident
9	facebook	fire
10	facebook	accident

filter

Filter

fire

accident

when I select fire I need out put like below

count(company)

when I select Accident I need out put like below

count(company)

when I select both fire and accident I need count like below

count(company)

Regards

Sathish

Re: how to achive both columns data we have in some other column data using filter.

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:52:16 GMT

What is the logic behind for last Count is 2? I know your intention but need to understand in your way

Re: how to achive both columns data we have in some other column data using filter.

satheshreddy — Thu, 12 Oct 2017 06:55:05 GMT

Hi Anil,

count 2 is how many company's we have both policy's.

Regards

Sathish

Re: how to achive both columns data we have in some other column data using filter.

Anil_Babu_Samineni — Thu, 12 Oct 2017 07:04:55 GMT

May be this?

=If(GetSelectedCount(policy) = 1, Count(policy),Count(Aggr(Count({<policy = {'fire', 'accident'}>} policy), policy)))

Re: how to achive both columns data we have in some other column data using filter.

tresB — Thu, 12 Oct 2017 07:24:54 GMT

Ideally it's AND-Mode issue as I already mentioned. However, you can also try it with a-kind of alternate approach (set analysis) which would be not so clean approach. When you select two types your output is against a to-be expression like:

=Count( DISTINCT {<company=p({<policy={'fire'}>})>*<company=p({<policy={'accident'}>})>} company)

For multiple values and to make it dynamic, i.e. when more types are selected, the expression has to be like:

=Count( DISTINCT {<company=p({<policy={'fire'}>})>*<company=p({<policy={'accident'}>})>*<company=p({<policy={'AnotherType'}>})>} company)

and so on,...

You could create a variable to generate this all-type combination in the expression with p(), like:

Variable2 : Concat(DISTINCT '<company=p({<policy={'&chr(39)& policy& chr(39)&'}>})>', '*')

And another variable for final expression

Variable1 : ='Count(DISTINCT {'&$(= Variable2)&'} company)'

Expand the variable in a text box: =$(Variable1)

Re: how to achive both columns data we have in some other column data using filter.

antoniotiman — Thu, 12 Oct 2017 08:14:14 GMT

Hi,

try this

=Count({<company={"=count(DISTINCT policy)=Getselectedcount(policy)"}>} DISTINCT company)

Re: how to achive both columns data we have in some other column data using filter.

tresB — Thu, 12 Oct 2017 08:27:45 GMT

Clever One!