topic Re: Comparing value in a variable in QlikView

Comparing value in a variable

Tue, 25 Jul 2017 08:52:47 GMT

Hi All,

I'm try to calculate a count of application which have 'vtestonclock' value greater than 30 via the following formula:

count ({$<$(vtestonclock)={">$(= 30)"},[Meeting]={'Full'},

, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

It works when i'm putting in the value via dimension 'Taken_OnClock' (instead of 'vtestonclock'). But, doesn't work when i use variable 'vtestonclock'.

Can someone please help me with this (how to correctly put in the variable)?

Any help will be much appreciated.

Kind Regards,

Kshitij

Re: Comparing value in a variable

jmvilaplanap — Tue, 25 Jul 2017 09:14:46 GMT

Hi,

Have you tried to write it between ''?

count ({$<'$(vtestonclock)'={">$(= 30)"},[Meeting]={'Full'},

, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

or with equal?

count ({$<$(=vtestonclock)={">$(= 30)"},[Meeting]={'Full'},

, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

Re: Comparing value in a variable

Tue, 25 Jul 2017 09:23:13 GMT

Hi Jose,

Thanks for your response. I tried both the options but none of them seems to work. Still getting no output.

As i mentioned in my original post, when i use 'Taken_OnClock' it gives me the correct response.

count ({$<Taken_OnClock={">$(= 30)"},[Meeting]={'Full'},

, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

Kind Regards,

Kshitij

Re: Comparing value in a variable

Kushal_Chawda — Tue, 25 Jul 2017 09:30:01 GMT

what is expression in vtestonclock?

Re: Comparing value in a variable

Tue, 25 Jul 2017 09:36:51 GMT

Hi Kushal,

It's a simple if statement which is giving me numbers.in output.

if(len($(vtest))=0,Taken_OnClock,$(vtest))

Regards,

Kshitij

Re: Comparing value in a variable

mangalsk — Tue, 25 Jul 2017 09:41:12 GMT

Define variable vtestonclock='Taken_OnClock', field name in quotes and in expression no need of using quote as follows

count ({$<$(vtestonclock)={">$(= 30)"},[Meeting]={'Full'},

, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

Re: Comparing value in a variable

Tue, 25 Jul 2017 09:49:20 GMT

Hi Mangal,

Unfortunately, i cannot assign direct value vtestonclock='Taken_OnClock'. I have to use either 'Taken_OnClock' or '$(vtest)' . If the value in $(vtest) is null, then i take 'Taken_OnClock' value (which i'm doing in a if statement). Value obtained from the if statement is being stored in 'vtestonclock'.

if(len($(vtest))=0,Taken_OnClock,$(vtest))

Hope it makes sense.

Re: Comparing value in a variable

mangalsk — Tue, 25 Jul 2017 11:30:34 GMT

It is ok so you can append quotes on this like ='''&if(len($(vtest))=0,Taken_OnClock,$(vtest))&''' or =if(len($(vtest))=0,'Taken_OnClock','$(vtest)') and rest as said before.

Basically it needs to store as string

Re: Comparing value in a variable

Kushal_Chawda — Tue, 25 Jul 2017 11:37:32 GMT

use below expression, your expression should work

=if(len($(vtest))=0,'Taken_OnClock','$(vtest)')

Use single quotes for your field names

Re: Comparing value in a variable

Tue, 25 Jul 2017 12:16:15 GMT

Hi Mangal,

It doesn't work. I tried putting single quotes and then double quotes (and vice versa) and then the & sign. But, it doesn't seem to work.

'"&if(len($(vtest))=0,Taken_OnClock_HREC,$(vtest))&"'

Any more help will be much appreciated.

Re: Comparing value in a variable

Tue, 25 Jul 2017 12:21:29 GMT

Hi Kushal,

No luck. when i try to use the expression as suggested by you it doesn't work. everything coming in as '-'

Regards,

Kshitij

Re: Comparing value in a variable

Kushal_Chawda — Tue, 25 Jul 2017 12:26:58 GMT

Please share sample file to work on

Re: Comparing value in a variable

Miguel_Angel_Baeyens — Tue, 25 Jul 2017 12:29:26 GMT

The left side of the set modifier cannot be an expression, it must be a field name.

However, you can use the advanced query syntax to get the values you are looking for, in the form of

count ({$<Taken_OnClock={"=if(len($(vtest))=0,Taken_OnClock,$(vtest))"},[Meeting]={'Full'}, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

I just copied your expression but you could use anything else for that, like a different field:

Count({$< Application_ref = {"=Sum(Field) > 30"}, [Meeting] = {'Full'}, [Application ] = {'Application -site'}, [Current ] ={'Approved', 'Further approved'} >} DISTINCT Application_ref)

Will search for Application_ref where Sum(Field) greater than 30 is true. That can be changed by any other working expression.

Re: Comparing value in a variable

Tue, 25 Jul 2017 12:57:51 GMT

is it possible to share it via any other way rather than uploading it on the forum?

Re: Comparing value in a variable

Kushal_Chawda — Tue, 25 Jul 2017 13:01:56 GMT

refer to below link, you can scramble your data and share it here

Preparing examples for Upload - Reduction and Data Scrambling

Re: Comparing value in a variable

effinty2112 — Tue, 25 Jul 2017 13:03:25 GMT

HI,

Maybe

count ({$<$(=vtestonclock)={">30"},[Meeting]={'Full'},

, [Application ]={'Application -site'},[Current ]={'Approved','Further approved'}>} DISTINCT Application_ref)

Re: Comparing value in a variable

mangalsk — Wed, 26 Jul 2017 05:27:13 GMT

can you just send a dummy file, i am wondering what is missed