topic Re: Expression to compare to fields in a table in QlikView

Expression to compare to fields in a table

Mon, 05 Jan 2015 05:05:51 GMT

Hello,

I have a table with two fields ID1, ID2.

I’m looking for an expression to find all entries where ID1 <> ID2.

In the following inline, I would like to ‘earmark’ rows 3,17.

t:
load
RowNo() as RowId,
*
;
LOAD * INLINE [
ID1, ID2
1, 1
2, 2
3, 2
4, 4
5, 5
6, 6
7, 17
8, 8
9, 9
];

Thanks,

Shaul

Re: Expression to compare to fields in a table

jagan — Mon, 05 Jan 2015 05:22:00 GMT

Hi,

Try like this

t:
load
RowNo() as RowId,
*,

If(ID1 <> ID2, 1, 0) AS Flag
;
LOAD * INLINE [
ID1, ID2
1, 1
2, 2
3, 2
4, 4
5, 5
6, 6
7, 17
8, 8
9, 9
];

Now in set analysis you can use flag field to find count() or sum().

=Count({<Flag={1}>} ID1)

Hope this helps you.

Regards,

Jagan.

Re: Expression to compare to fields in a table

jonathandienst — Mon, 05 Jan 2015 05:40:55 GMT

To filter:

load

RowNo() as RowId,

where ID1 <> ID2

;

LOAD * INLINE [

ID1, ID2

1, 1

2, 2

3, 2

4, 4

5, 5

6, 6

7, 17

8, 8

9, 9

];

To flag matches/mismatches:

load

RowNo() as RowId,

If(ID1 = ID2, 1, 0) As MatchFlag

;

LOAD * INLINE [

ID1, ID2

1, 1

2, 2

3, 2

4, 4

5, 5

6, 6

7, 17

8, 8

9, 9

];

HTH

Jonathan

Re: Expression to compare to fields in a table

its_anandrjs — Mon, 05 Jan 2015 08:46:29 GMT

Hi,

Try this ways

load
RowNo() as RowId,
if(ID1=ID2,'Match Record','UnMatch Record') as IdDetails, //For Finding flag (Match and MisMatch)
if(ID1=ID2,1 , 0 ) as Flag //For SET analysis calculation
*;
LOAD * INLINE [
ID1, ID2
1, 1
2, 2
3, 2
4, 4
5, 5
6, 6
7, 17
8, 8
9, 9
];

Regards

Anand

Re: Expression to compare to fields in a table

Mon, 05 Jan 2015 17:03:09 GMT

Thanks for the tip Jonathan. What I am looking for is a Chart/Expression solution as opposed to a Scripting one. I was wondering if it is doable to compare two columns in a QV chart. Thanks! Shaul

Re: Expression to compare to fields in a table

Mon, 05 Jan 2015 17:04:07 GMT

Thanks for the tip Anand. What I am looking for is a Chart/Expression solution as opposed to a Scripting one. I was wondering if it is doable to compare two columns in a QV chart. Thanks! Shaul

Re: Expression to compare to fields in a table

Mon, 05 Jan 2015 17:05:37 GMT

Thanks for the tip Jagan. What I am looking for is a Chart/Expression solution as opposed to a Scripting one. I was wondering if it is doable to compare two columns in a QV chart. Thanks! Shaul

Re: Expression to compare to fields in a table

Mon, 05 Jan 2015 17:05:45 GMT

try

where not exists(ID1,ID2)

Re: Expression to compare to fields in a table

Mon, 05 Jan 2015 17:07:24 GMT

ahhhh.

then in the chart write an expresson like this

=IF(ID1<>ID2, whateveryouwant2returniftheymatch)

notice that I didn't include else.

Re: Expression to compare to fields in a table

rwunderlich — Mon, 05 Jan 2015 17:10:07 GMT

Wouldn't that just be an expression:

ID1 <> ID2

Or is it more complex than that?

-Rob

Re: Expression to compare to fields in a table

Mon, 05 Jan 2015 17:11:10 GMT

Thanks!

Re: Expression to compare to fields in a table

preminqlik — Mon, 05 Jan 2015 17:12:17 GMT

hi there,

use this expression in chart !

only({<ID1={'=ID1<>ID2'}>}ID2)

find the attachment

Re: Expression to compare to fields in a table

Wed, 28 Jan 2015 16:36:38 GMT

Quick follow up to this...what if the fields were in 2 different tables? What is an expression that would return any value in either table/field that is not a value in the the first table/field?

In other words, just list every value that is in one field but not in the other field...for both fields.

(not using a load script as this could be done there with where not exists)

Looking for an expression.

Thanks.