Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I want to compare two sheets (with similar structure) in Qlik, using the values of a column as starting condition to make the comparison. This is an example to make myself clear:
Sheet 1
Id Color Size Status
A1 Red L Good
B3 Red M Bad
F2 Green S Good
Sheet 2
Id Color Size Status
D7 Green L Good
F2 Red M Bad
A1 Green S Good
Now, I want to make a comparison for every column (checking if the values are the same), using Id column as precondition: if an Id of Sheet 1 is equal to an Id of Sheet 2, compare each value of the same row matching with that Id.
In this particular case, there is Id A1 in both sheets, so we can compare the other columns, with these results:
- Color -> False (Sheet1: Red, Sheet2: Green)
- Size -> False (Sheet1: L, Sheet2: S)
- Status -> True (Sheet1: Good, Sheet2: Good)
Instead, for Id B3 the comparison will immediately return false because there is no Id B3 in Sheet 2.
I implemented this comparison in Excel using this formula:
=IFERROR(--EXACT(VLOOKUP($A2;'Sheet2'!$A$2:$D$4;COLUMN(A1)+1;0);B2);"false")
How can I implement this comparison in Qlik?
Load Id, Color, Size, Status From Sheet1;
JOIN
Load Id, Color as Color2, Size as Size2, Status as Status2 From Sheet2;
You can then just compare Color to Color2, etc.
Load Id, Color, Size, Status From Sheet1;
JOIN
Load Id, Color as Color2, Size as Size2, Status as Status2 From Sheet2;
You can then just compare Color to Color2, etc.