topic Apply exists function only on applicable result set in QlikView

Apply exists function only on applicable result set

Tue, 22 Mar 2011 10:27:25 GMT

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

Apply exists function only on applicable result set

Miguel_Angel_Baeyens — Tue, 22 Mar 2011 10:48:39 GMT

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.

Apply exists function only on applicable result set

Tue, 22 Mar 2011 11:15:18 GMT

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

Apply exists function only on applicable result set

rbecher — Tue, 22 Mar 2011 11:45:10 GMT

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

Apply exists function only on applicable result set

rbecher — Tue, 22 Mar 2011 12:07:05 GMT

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

- Ralf

Apply exists function only on applicable result set

Tue, 22 Mar 2011 12:42:13 GMT

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

Apply exists function only on applicable result set

rbecher — Tue, 22 Mar 2011 12:50:32 GMT

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

Apply exists function only on applicable result set

rbecher — Tue, 22 Mar 2011 12:55:44 GMT

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

- Ralf

Apply exists function only on applicable result set

Tue, 22 Mar 2011 13:47:42 GMT

Thanks a lot for the info!