Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
paste the lnk to the J Witherspoon's advice please:)
What do you mean by row by row comparison here?Can you explain it?
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
;
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
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
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
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
Joggie
You probably dont need the %Key2 field, Just remove that line from the script.
Regards
Jonathan