Apply exists function only on applicable result set

[#1]

Dears,

I was wondering if it is possible to use the 'exits()' function to only check within the possible values. Example:

[CODE]

aTable:
load
*,
if(Exists(field_to_search_in,other_field_current_value),'Yes', 'No') as [Result]
resident
bTable

[/CODE]

The problem is the fact that the [Result] field will also hold 'Yes' values for results that are not applicable on the current selection criteria. Compare my problem with the set analysis functionality to use $ or 1 to define what should be taken into account. I only want the outcome to be 'Yes' if the fieldvalue was found in the possible value list of 'field_to_search_in'.

Hope my problem is clear. Thanks in advance!

Ps: Any other approaches to solve the problem are of course very welcome 🙂

Kind regards,

Maarten

[Miguel_Angel_Baeyens]

Hello Maarten,

You are using Exists() in the script, where there are no selections done yet (there will be once the document is completely and successfully loaded), and all values are possible. You need to define some other condition for that "was found in the possible value list of 'field_to_search_in'".

If you post some sample data and expected results will help to find out a solution.

Hope that helps.

[#3]

Hi Miguel,

I tried to make it as simple as possible in the example...

We are working with massive datasets, so I had to create an inline simulation.

The expected results are described within the QV document.

Thanks in advance!

Regards,

Maarten

[rbecher]

Hi Maarten,

you could split the field rslt_phonelog by using the function SubField( ). See my attached version. I've added also some records to show how it works.

- Ralf

[rbecher · Accepted solution]

..I've changed the example, It was not complete.

- Ralf

[#6]

Hi Ralf,

Spot on! Exactly the outcome I was hoping for! Great job! Thanks!

The only thing I was wondering when taking a look at your solution, is what the impact will be when treating massive amounts of data... aspecially due to the use of Sync keys... What are your thoughts one that?

Thanks again!

Regards,

Maarten

[rbecher]

Hi Maarten,

you could remove the key fields (slt_id, rslt_property) on the ResultDetail table. I kept it only for explanation.

ResultDetail:
load rslt_id & '_' & rslt_property & '_' & rslt_log_detail as rslt_log_detail_key;
load
rslt_id,
rslt_property,
SubField(rslt_phonelog, ',') as rslt_log_detail
resident Result;

- Ralf

[rbecher]

..and also you can drop the initial table Result.

- Ralf

[#9]

Thanks a lot for the info!