Row by Row Comparison

[joggiek]

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

[#2]

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

[CELAMBARASAN]

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

[joggiek]

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

;

[joggiek]

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

[#6]

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

[jonathandienst · Accepted solution]

Hi

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

[joggiek]

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

[jonathandienst]

Joggie

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

Regards

Jonathan