topic Re: Comparing two tables in QlikView

Comparing two tables

Anonymous — Mon, 31 Aug 2015 18:45:31 GMT

Hi, I have to tables which should have exactly the same data. I need to find the columns that don’t match base on the same ID. See examples below. Any idea what's the best way to complete this task using qlikview? I'm using QV personal edition, so please explain the method if possible.

Thanks!

Table1

ID	Field1	Date	Value
1234	a14	12/13/15	$1245
5677	r12	3/6/15	$2561
2356	j36	2/4/13	$252

Table2

ID	Field1.A	Date.A	Value.A
1234	a14	12/13/15	$1245
5677	t36	3/16/15	$2561
2356	j36	12/4/13	$252

Re: Comparing two tables

petter — Mon, 31 Aug 2015 18:58:59 GMT

Do you want to do it in the Load Script or in the GUI via a chart and expressions?

Re: Comparing two tables

rwunderlich — Mon, 31 Aug 2015 19:01:04 GMT

Doing this in a Straight Table should be fairly easy. Make a Straight Table Chart with DImensions of ID, FIeld1, Field1.A, Date, Date.a, Value, Value.A.

The Chart Expression, to limit the chart to only rows that have differences would be:

Field1 <> Field1.A OR Date <> Date/A OR Value <> Value.A

To highlight which fields are different, lick the + next to each Dim and use this for the Background Color.

if(Field1 <> Field1.A, yellow())

etc for the remaining DIms.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Comparing two tables

Anonymous — Mon, 31 Aug 2015 19:01:46 GMT

No preference. Currently, I'm manually checking in a straight table using expression "if (Field1 = Field1.A,'Y','N')". I'm open to try other better method, either load script or chart expression.

Thanks!

Re: Comparing two tables

Qrishna — Mon, 31 Aug 2015 19:04:37 GMT

is this the output what you are expecting?

Re: Comparing two tables

petter — Mon, 31 Aug 2015 19:08:05 GMT

A Load Script version of it:

Table1:

LOAD * INLINE [

ID, Field1, Date, Value

1234, a14, 13.12.2015, 1245

5677, r12, 6.3.2015, 2561

2356, j36, 4.2.2013, 252

];

JOIN (Table1)

Table2:

LOAD * INLINE [

ID, A.Field1, A.Date, A.Value

1234, a14, 13.12.2015, 1245

5677, t36, 16.3.2015, 2561

2356, j36, 4.12.2013, 252

];

Qualify *;

Result:

LOAD

ID,

If( Field1 <> A.Field1 , Field1 , '') AS Field1,

If ( Date <> A.Date , Date , '' ) AS Date,

If ( Value <> A.Value , Value , '') AS Value,

If( Field1 <> A.Field1 , A.Field1 , '') AS A.Field1,

If ( Date <> A.Date , A.Date , '' ) AS A.Date,

If ( Value <> A.Value , A.Value , '') AS A.Value

RESIDENT

Table1

WHERE

Field1 <> A.Field1

OR Date <> A.Date

OR Value <> A.Value

;

Re: Comparing two tables

Anonymous — Mon, 31 Aug 2015 19:12:29 GMT

Hi Rob

In a straight table, is there's a way to reference the column number instead of the actual field name in the expression? I'm thinking an expression like "if( column (2) = column (3),'Y','N') " instead of "if (Field1 = Field1.A,'Y','N')". This way I don’t need to rewrite the expression every time I compare another pair of fields. I actually have a lot of field to compare…. Thanks

Yvonne

Re: Comparing two tables

petter — Mon, 31 Aug 2015 19:14:51 GMT

In a Straight Table Chart - Add ID as Dimension and all Six Fields as their Names (just like the column labels on the screen shot:

Re: Comparing two tables

petter — Mon, 31 Aug 2015 19:29:22 GMT

Yes you can use an expression exactly like that. There is a function Column(n) where n is the column-number your want to reference...

Re: Comparing two tables

Anonymous — Mon, 31 Aug 2015 19:31:30 GMT

Hmmm. But it doesn't work for me, I thought the column(n) is to refer measures only, not for dimension/field, no?

Re: Comparing two tables

petter — Mon, 31 Aug 2015 19:39:24 GMT

But it is only the ID that needs to be a dimension. The Fields can be listed as expressions. The expressions does not need to be measures/metrics but can also be strings. In the example I sent you I didn't use any aggregate function around the field-names and that will work as long as you always have one and only one distinct ID for each row.