topic Re: Compare two fields in a chart in QlikView

Compare two fields in a chart

Anonymous — Tue, 04 Aug 2015 15:03:46 GMT

I have two fields of IDs : IDA and IDB I want to compare in a chart. If value of IDA appears in IDB, then mark it valid. The chart should look like this. I think there should be an expression I can write for the "Valid" column, can someone help with that?

Thanks

IDA	Valid
251243	Yes
12613	NO
1543	Yes

Re: Compare two fields in a chart

maximiliano_vel — Tue, 04 Aug 2015 15:06:04 GMT

Can you send the IDB values?

Re: Compare two fields in a chart

Anonymous — Tue, 04 Aug 2015 15:37:30 GMT

For example:

251243,

12612,

1543

Re: Compare two fields in a chart

maximiliano_vel — Tue, 04 Aug 2015 15:48:32 GMT

Try this

FieldsTmp:

LOAD * INLINE [

IDA, IDB

251243, 1543

12613, 251243

1543, 12612

];

ToCompare:

LOAD IDB as CompareTo

RESIDENT FieldsTmp;

NOCONCATENATE

Comp:

LOAD IDA,

IDB,

if(Exists(CompareTo, IDA), 'Yes', 'No') as Valid

RESIDENT FieldsTmp;

DROP Table FieldsTmp, ToCompare;

Re: Compare two fields in a chart

Anonymous — Tue, 04 Aug 2015 17:52:14 GMT

Anyway this can be done in the chart instead in the load script? I was trying to avoid using load script because there are a lot of fields I'm trying to compare. I feel an expresison in the chart will be easier.

thanks

Re: Compare two fields in a chart

maximiliano_vel — Tue, 04 Aug 2015 18:00:22 GMT

If the columns IDA and IDB are like

IDA	IDB
12345	12345
1262	1263
3425	3425

Then the Valid Column shoud be something like

=f([IDA]=[IDB], 'Yes', 'No')

Re: Compare two fields in a chart

Anonymous — Tue, 04 Aug 2015 18:48:40 GMT

Actually, IDA and IDB are fields from two differnet tables. How should the expression be modified?

Thanks

Re: Compare two fields in a chart

Kushal_Chawda — Tue, 04 Aug 2015 18:55:13 GMT

Create calculated dimesion

=aggr(if(trim(lower(IDA))=trim(lower(IDB)),'Valid','Invalid'), Dimension)

or Expression

if(trim(lower(IDA))=trim(lower(IDB)),'Valid','Invalid')