topic Re: how to compare two field values in QlikView

how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 10:30:44 GMT

Hello All,

I need to write a expression in set analysis which should compare between two fields values let say

if field1 value = field2 value and field2 value has more than one IP then sum of values(field2=count(distinct ip)>1)

How to write above condition in set analysis plz...

Thanks

Re: how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 10:41:02 GMT

Any Suggestions plz

Re: how to compare two field values

tresB — Thu, 20 Jul 2017 10:42:52 GMT

May be like:

Sum({<KeyField={"=field1=field2"}, field2={"=count(distinct ip)>1"}>}Value)

Note: KeyField here is a field that would uniquely identify the records in the table.

Re: how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 10:48:36 GMT

thanks for reply,

actually KeyField is field1 i want to check whether field1 values are present in field2 , if they are present then i need to check whether the values has more then one ip or not

Re: how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 10:49:54 GMT

is this right ?

Sum({<field1={"=field2"}, field2={"=count(distinct ip)>1"}>}Value)

Re: how to compare two field values

tresB — Thu, 20 Jul 2017 10:53:17 GMT

Try:

Sum({<field1={"=field1=field2"}, field2={"=count(distinct ip)>1"}>}Value)

and yes based on your requirement, you might have to put the same set condition in the inner count() as well, like:

Sum({<field1={"=field1=field2"}, field2={"=count({<field1={"=field1=field2"}>}distinct ip)>1"}>}Value)

Re: how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 10:57:04 GMT

if i need to write not equal then i mean if field1 values should not be in field2 ,

Sum({<field1<>{"=field1=field2"}, field2={"=count({<field1={"=field1=field2"}>}distinct ip)>1"}>}Value)

is above expression right for not present or not equal

Re: how to compare two field values

tresB — Thu, 20 Jul 2017 11:04:17 GMT

You could try:

Sum({<field1={"=field1<>field2"}, field2={"=count({<field1={"=field1<>field2"}>}distinct ip)>1"}>}Value)

However, let me tell you, field1<>field2 is not same as

'if field1 values should not be in field2'

For that you might have to use e()

Re: how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 11:19:51 GMT

sorry, may be my english is not good actually what i need to show is

one table

show only values which are present in both the fields and ip>1

second table show remaining i mean

show all the field1 values which or not present in field2 and ip=1 or null

Re: how to compare two field values

kunkumnaveen — Thu, 20 Jul 2017 11:39:40 GMT

trying to write possible function but throwing error

sum({<field1=p({<field2>}),field2={"count(distinct ip)>1"}value)

Re: how to compare two field values

Anil_Babu_Samineni — Thu, 20 Jul 2017 11:45:03 GMT

Do like below, May be?

sum({<field1=p({<field2 = {"=Count(DISTINCT ip) > 1"}>} field2) >}value)

Re: how to compare two field values

tresB — Thu, 20 Jul 2017 11:54:11 GMT

Try to share a sample qvw and explain expected output against that.