topic Row by Row Comparison in QlikView

Row by Row Comparison

joggiek — Tue, 24 Jul 2012 14:35:42 GMT

Hi,

I looked at the other posts concerning similar problems, but still can get my model to work correctly.

I need to show all the differences per row in a table similar to the attached excel file

I tried modifying J Witherspoon's code but still no joy

Thanks

Re: Row by Row Comparison

Tue, 24 Jul 2012 15:58:18 GMT

paste the lnk to the J Witherspoon's advice please:)

Re: Row by Row Comparison

CELAMBARASAN — Tue, 24 Jul 2012 16:41:41 GMT

What do you mean by row by row comparison here?Can you explain it?

Re: Row by Row Comparison

joggiek — Tue, 24 Jul 2012 19:04:19 GMT

Table:

LOAD *

,'Table 1' as Table

INLINE [

OrderItem, Customer, Amount

OI1, Ann, 500

OI2, Bob, 600

OI3, Charlie, 2000

OI4, Dennis, 200

OI6, Fred, 500

OI5, Erin, 500

];

CONCATENATE

LOAD *

,'Table 2' as Table

INLINE [

OrderItem, Customer, Amount

OI2, Bobby, 600

OI3, Charlie, 200

OI4, Dennis, 200

OI5, Erin, 550

OI6, Fred, 500

];

INNER JOIN (Table)

LOAD *

WHERE "Only in One Table?"

OR "Customer Different?"

OR "Amount Different?"

;

LOAD

OrderItem

,if(count(OrderItem)<2,-1) as "Only in One Table?"

,if(maxstring(Customer)<>minstring(Customer),-1) as "Customer Different?"

,if(max(Amount)<>min(Amount),-1) as "Amount Different?"

RESIDENT Table

GROUP BY OrderItem

;

Re: Row by Row Comparison

joggiek — Tue, 24 Jul 2012 19:06:30 GMT

I need to load a few thousand rows every month, sometimes the data stays the same across all columns, other times some of the data changes, I need to only show the rows of data that has changed

Re: Row by Row Comparison

Tue, 31 Jul 2012 12:18:00 GMT

Hi Joggie

Hope it helps!

Production:
LOAD Field1,
     Field2,
     Field3,
     Field1&Field2&Field3 AS %Key1
FROM
Table1.qvd
(qvd);
 
Development:
LOAD Field1,
     Field2,
     Field3,
     Field1&Field2&Field3AS %Key2 
FROM
Table2.qvd
(qvd)
WHERE NOT EXISTS(%Key1,Field1&Field2&Field3)
;
Drop Table Production;

Thanks,

Attitude

Re: Row by Row Comparison

jonathandienst — Tue, 31 Jul 2012 12:22:43 GMT

One change to the above script (which will not work properly due to automatic concatenation):

Production: LOAD Field1, Field2, Field3, Field1&Field2&Field3 AS %Key1 FROM Table1.qvd (qvd); Development:

NoConcantenate LOAD Field1, Field2, Field3, Field1&Field2&Field3AS %Key2 FROM Table2.qvd (qvd) WHERE NOT EXISTS(%Key1,Field1&Field2&Field3) ; Drop Table Production;

Regards

Jonathan

Re: Row by Row Comparison

joggiek — Tue, 31 Jul 2012 12:28:19 GMT

Thank you,

I think it will work, it just creates a big dimension from the key, I thought/hoped there might be another way.

Cheers

Joggie

Re: Row by Row Comparison

jonathandienst — Tue, 31 Jul 2012 12:36:06 GMT

Joggie

You probably dont need the %Key2 field, Just remove that line from the script.

Regards

Jonathan