Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

how to compare two field values

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

11 Replies
kunkumnaveen
Contributor III

Re: how to compare two field values

Any Suggestions plz

MVP
MVP

Re: how to compare two field values

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.

kunkumnaveen
Contributor III

Re: how to compare two field values

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

Highlighted
kunkumnaveen
Contributor III

Re: how to compare two field values

is this right ?

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

MVP
MVP

Re: how to compare two field values

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)

kunkumnaveen
Contributor III

Re: how to compare two field values

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

MVP
MVP

Re: how to compare two field values

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()

kunkumnaveen
Contributor III

Re: how to compare two field values

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

kunkumnaveen
Contributor III

Re: how to compare two field values

trying to write possible function but throwing error

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