Set analysis for retrieving name that matches criteria

[LukeWarm1989]

Hello,

Need to some help! I have an expression I use within QLIK to retrieve a name from a table, it looks like this -

Only({<PIRole_Description={'Adjuster'}>} IF(PIRole_OrganisationName = 'N/A',(PIRole_Firstname &' '& PIRole_Surname),PIRole_OrganisationName))

So basically it looks at the parties involved and returns the name of the 'Adjuster' from the table of all parties involved.

Now the problem I have come across is sometimes there is a case where there is also another party involved who also has 'Adjuster' in the role description along with another role description - e.g. 'Adjuster' & 'Supplier'.

On these cases the original expression does not work and returns a blank. I believe it is due to the above when there is more than one adjuster in a role.

Is there a way to specifically only look at the cases where there is only 1 'role description'? 

Help is much appreciated!

I've only got access to QLIK Sense and can't change any LOADs 😞

[Andrei_Cusnir]

Hello,

Can you please try the following expression:

Only({<PIRole_Description={'*Adjuster*'}>} IF(PIRole_OrganisationName = 'N/A',(PIRole_Firstname &' '& PIRole_Surname),PIRole_OrganisationName))

I have just added the symbol * after and before the word "Adjuster". In general the set analysis that you have, will take into consideration all the values where  PIRole_Description are exactly "Adjuster". However if you have values such as "Supplier & Adjuster" or "Adjuster & Supplier" then those values don't match with the criteria. 

Adding the *, tells the set analysis that it is important to find the values where the word "Adjuster" is present, regardless if it is in the beginning or at the end.

I hope that this information was helpful. 

[LukeWarm1989]

Hey Andrei,

Funnily enough was just trying the above before I refreshed - unfortunately no change.

apologies I might not of been super clear on my original post and maybe this will help;

 If a party has been assigned multiple descriptions  it's like a tick box rather than a string of two together. So it would be as if 1 user has ticked 'Adjuster' and also 'Supplier' this is what I think is causing the issue but i would have though it would just return both matches across two rows??

Do you think using a count of descriptions would work? only return a value if the count = 1

[Andrei_Cusnir]

Hello @LukeWarm1989,

Thank you very much for your clarification. It seems that the use case scenario is slightly more complicated than what I have imagined. In this case, I would recommend you the following:

1. Please create an EXCEL with few fake data entries. No need to use real values, just reconstruct the PIRole_Description, PIRole_OrganisationName, PIRole_Firstname, PIRole_Surname and PIRole_OrganisationName columns.
2. Load the data into a new sample app
3. Preproduce the issue with the fake data
4. Upload the app here

This will allow me and other community members that are reading the post to test the sample app with the fake data on our side and we will be able to see where exactly this expression is used and what exactly seems to be the issue. Please also include steps to reproduce the issue, such as "Select value A, B and C then click on the button and you will notice that the records D, E and F are ignored" etc.. If any of us will be able to find a workaround, then we will share it with you or maybe we will be able to at least share with you the next steps moving forward!