topic Re: Exists function differs.. in QlikView

Exists function differs..

Mon, 10 Mar 2014 07:01:19 GMT

Hi there,

Here is a sample script for exists function.

Tab1:

Load year,

Month,

if(exists(Month,'jan'),1,0) as firstsale;

LOAD * INLINE [

year, Month, Sales

2012, jan, 10

2012, feb, 20

2012, mar, 30

2013, jan, 10

2013, feb, 20

2013, mar, 30

];

It is giving 0 for the 'jan' month, but actually it has to return 'true which is 1 in my case. Can anyone tell why it is giving reverse?

Re: Exists function differs..

tresB — Mon, 10 Mar 2014 07:05:17 GMT

Probably you need, Match(). Exists() is for something else. try like:

if(Match(Month,'jan'),1,0) as firstsale;

Re: Exists function differs..

Mon, 10 Mar 2014 07:08:38 GMT

Yes Match function will work. but actually i try to analyse the exists functionality..!

Exists(field,exp) - will that 'exp' evaluating only for the first time?

Re: Exists function differs..

tresB — Mon, 10 Mar 2014 07:12:47 GMT

Not really. Exists() - determines whether a specific field value exists in a specified field of the data loaded so far. That means, for first time 'jan' the fucntion retuurns FALSE because so far no 'jan' is loaded; once it is loaded, returns TRUE for all subsequent occurances of 'jan'.

Re: Exists function differs..

CELAMBARASAN — Mon, 10 Mar 2014 07:15:49 GMT

Exists will be check the current value previously loaded table field values, not within the table.

Example:

LOAD * Inline [

MonthName

jan

mar];

LOAD *, if(Exists(MonthName, Month), 1, 0) AS isExists INLINE [

year, Month, Sales

2012, jan, 10

2012, feb, 20

2012, mar, 30

2013, jan, 10

2013, feb, 20

2013, mar, 30

];

OutPut:

year, Month, Sales, isExists

2012, jan, 10, 1

2012, feb, 20, 0

2012, mar, 30, 1

2013, jan, 10, 1

2013, feb, 20, 0

2013, mar, 30, 1

It is 1 for Jan and Mar only

Here exists checks the Month field in current table and checks with the MonthName field values for match.

MonthName is a field which is already loaded in to the memory.

Re: Exists function differs..

Mon, 10 Mar 2014 07:20:55 GMT

Thank you...I got it...

Re: Exists function differs..

Mon, 10 Mar 2014 21:06:27 GMT

Exists function can fetch the values with in the same table. We can use "where not exists" for fetching "distinct" rows in the same table.