Help! Comparing values in different fields

[louise_atherton]

Hiya

I want to compare values in two different fields using an expression in Qlik:-

1)  [Audits: Vehicle registration]

2) [Risk Assessments: Vehicle registration index]

Both these fields display vehicle registrations and I want to be able to compare  [Audits: Vehicle registration] to [Risk Assessments: Vehicle registration index], to identify which vehicles from the index has not been inspected that day.

Thanks in advance for any suggestions as I'm a bit stumped. 

Louise 

[JordyWegman]

Hi Louise,

Do you want to do this in the front-end of Qlik Sense? Or do you have the possibility to do this in the back-end?

This would make a bit easier for you and gives you more possibilities in the front-end (like selecting the ones that are not inspected)

Jordy

Climber

[louise_atherton]

Hiya

No we have a restricted view of Qlik so we have to manage any data analytics using the basic 'edit expression' form functionality only 😞

Thanks

Louise 

[JordyWegman]

Hi Louise,

No problem, try this:

Count( Distinct {$< [Audits: Vehicle registration] = {"= [Audits: Vehicle registration] = [Risk Assessments: Vehicle registration index]"}>} [Audits: Vehicle registration] )

If the other way around, it's this:

Count( Distinct {$< [Risk Assessments: Vehicle registration index] = {"= [Risk Assessments: Vehicle registration index] = [Audits: Vehicle registration]"}>} [Risk Assessments: Vehicle registration index] )

I'm not sure which one you want to count so I have created both version.

Jordy

Climber

[JordyWegman]

This is a bit more explanation:

Count( Distinct {$< A = {"= A = B"}>} A )

This was the table I used and compared both with each other to see if they were there.

Though what you need to know if they are both on the same line.

If this is not the case, then we need a Possible function.

Jordy

Climber

[louise_atherton]

Hiya

Thanks for taking the time to respond.  Unfortunately the don't sit on the same line so effectively I am comparing all the values in one column against all the values in another column as they effectively sit in different modules.

Thanks

Louise 

[JordyWegman · Accepted solution]

Hi Louise,

Try this:

=count({$<A =P(B)>*$} A)

A and B are your field that you want to compare.

Jordy

Climber

[louise_atherton]

Hi, thanks for the suggestion. I'm just testing it now, but just out of curiosity.  What does the 'P' within the expression do? 

Thanks

Louise 

[louise_atherton]

Thanks so much!  That worked exactly as I needed it to and also was able to adapt to give me my exceptions report only.  Next question....How would I go about building into that formula a 'date created' filter based on using the 'today' function?   This is so I can only checks which have been completed today. 

Thanks again so much for your help. 

Louise 

[JordyWegman]

Hi Louise,

Check this link for the explanation: https://community.qlik.com/t5/QlikView-App-Dev/P-E-and-where-do-you-use-them/td-p/457847

It's a bit better then me explaining it all over again.

Jordy

Climber