Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joggiek
Partner - Contributor III
Partner - Contributor III

Row by Row Comparison

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
Not applicable

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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

joggiek
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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

Not applicable

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
joggiek
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Champion III
Partner - Champion III

Joggie

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

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein