Exists function differs..

[#1]

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?

[tresesco]

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

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

[#3]

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?

[tresesco · Accepted solution]

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'.

[CELAMBARASAN]

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.

[#6]

Thank you...I got it...

[#7]

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.