topic Re: To Capture a reason owing to specific type! in QlikView

To Capture a reason owing to specific type!

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

Dear Sunny or any one from our community,

This refers to some data arranged as per some prearranged plan. I need to apply reason where Branches visited=0. For that I have given a reason through variable overview window (VReason1 with definition 'owing to flood'. To pick this reason the expression given too presented here. But it looks like an error associates.

IF(Set_Month='Jun' and sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))=0,Concat(VReason1))

I prefer if Dear Sunny responds to this particularly because she has been continually proposing answers to this issue from the point where I started!

Neville

Re: To Capture a reason owing to specific type!

sunny_talwar — Mon, 06 Nov 2017 18:01:51 GMT

nevilledhamsiri‌

1) I am a "He" and not a "She"

2) How does it matter if I have been helping you out from the beginning, anybody should be able to help if you put the question in the correct way

3) What is the output you are expecting to see?

Re: To Capture a reason owing to specific type!

nevilledhamsiri — Mon, 06 Nov 2017 18:10:03 GMT

Sorry dear sunny, Your smaller picture looks like kind of she. It is alright. Now I need to bring in the reason for branches not visited which has taken 0 values in counting. For that I have given a reason as stated above in a variable. This 0 value taken in the Month of Jun. How I will make this reason appear before 0 value.

IF(Set_Month='May' and sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))=0,Concat(VReason1))

Re: To Capture a reason owing to specific type!

nevilledhamsiri — Mon, 06 Nov 2017 18:40:25 GMT

Please look in to this

Re: To Capture a reason owing to specific type!

nevilledhamsiri — Tue, 07 Nov 2017 15:07:24 GMT

Dear Experts!

I need to make appear the reasons I have defined through Vreason using variable overview window. The expression used as follows. But it seems not working. Please check & let me know the reasion

IF(Set_Month='Jun' and sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))=0,Concat(VReason1))

Vreason1____'Due ti Flood'

Re: To Capture a reason owing to specific type!

sunny_talwar — Tue, 07 Nov 2017 15:10:40 GMT

Share a qvw sample please

Uploading a Sample

Preparing examples for Upload - Reduction and Data Scrambling

Re: To Capture a reason owing to specific type!

nevilledhamsiri — Tue, 07 Nov 2017 15:17:15 GMT

Please see

Re: To Capture a reason owing to specific type!

Colin-Albert — Tue, 07 Nov 2017 15:19:31 GMT

Do you get a valid result if you replace Concat(VReason1) with text as a test?

e.g. Try IF(Set_Month='Jun' and sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))=0, 'AAA')

If you get AAA as the output, then your test works and the concat expression is wrong.

If you do not get AAA, then your "if" logic is wrong.

Try putting the different components of the if test into separate expressions to test then.

What do you get with the expression sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))

in a expression on its own? Are there other dimension that should be included in the aggr expression? Add Visit_Month and [Person to visit] perhaps.

Re: To Capture a reason owing to specific type!

sunny_talwar — Tue, 07 Nov 2017 15:24:26 GMT

So, for each of the row you would like to see 'Due to Flood'?

Re: To Capture a reason owing to specific type!

Colin-Albert — Tue, 07 Nov 2017 15:35:08 GMT

Try this

IF(Set_Month='Jun' and sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))=0, Type_Of_Visit & ' ' & VReason1)

Your example tests May but there is no palce Not Visited in May.

There is no need to use concat()

Re: To Capture a reason owing to specific type!

Colin-Albert — Tue, 07 Nov 2017 15:36:09 GMT

See Attached

Re: To Capture a reason owing to specific type!

nevilledhamsiri — Tue, 07 Nov 2017 16:10:54 GMT

Dear Colin,

This expression pick the reason I need. Simillarly I need to add any other reasons through variables such as for late visits etc can this expression be used if so what change be made to this expression.I have included the reason please suggest the expression to pick it up.

Many Thanks

Neville

Attached the qvw

IF(Set_Month='Jun' and sum(aggr(COUNT(DISTINCT([Person visited])),[place to visit],[Place visited]))=0, Type_Of_Visit & ' ' & VReason1)

Re: To Capture a reason owing to specific type!

nevilledhamsiri — Tue, 07 Nov 2017 16:16:53 GMT

Please find the attachment