create a year groups

[Kohli]

I have a Experience field in a table. I have to create a year wise experiance

Ex:         Experiance

          2 year 1 months 10 days

          1 year 7 months 15 days

          2 year 1 months 10 days

          2 year 1 months 10 days

         4 year 1 months 10 days

         1 year 1 months 10 days

         4 year 1 months 10 days

         2 year 1 months 10 days

         5 year 1 months 10 days

         3 year 1 months 10 days

         3 year 1 months 10 days

         4 year 1 months 10 days

         3 year 1 months 10 days

i want create group like exp 0-1 year

                                             1 -2 year

                                              3-4 year

                                               4-5 year

                                               >5 Years

Can you please suggest me..

[quriouss]

Look up the CLASS function.

=Class(experience, 1 , 'Experience')

Will give;

    0 <= Experience < 1

    1 <= Experience < 2

    Etc.

as data labels.

If you want to put limits (>5 Years in your example) then you can wrap it in an IF statement;

IF ([Experience] > 5 , '>5 Years' , Class(experience, 1 , 'Experience'))

but in that case I'd be tempted to do this with IF and DUAL in the load script;

LOAD

If ([Experience] > 5 , DUAL('>5 Years' , 1) ,

     If ([Experience] > 4 , DUAL ('4-5 year' , 2) ,

     If ([Experience] > 3 , DUAL ('3-4 year' , 3) ,

     If ([Experience] > 2, DUAL ('2-3 year' , 4) ,

     If ([Experience] > 1 , DUAL ('1-2 year' , 5) ,

     If ([Experience] > 0 , DUAL ('0-1 year' , 6) )))))) AS [Experience];