Grouping values of a field in load script

[#1]

Hi!

I have a table with ID and  WorkingHours fields. I need to gorup them like:

from 1 to 8 hours  as 1 Day

From 9 to 16 hours as  2 Days

From 17 hours to 25 hours as 3 Days

Any idea how can I do that in Load script?

[#2]

hi,

you can write an nested if condition to achieve this.

if(WorkingHours >=1 and WorkingHours <=8,'1 Day',

if(WorkingHours >=9 and WorkingHours <=16,'2 Days',

if(WorkingHours >=17 and WorkingHours <=25,'3 Days'))) as Group

HTH

regards

amay

[fvelascog72]

Try

Ceil([WorkingHours]/8)               as Days

[#4]

Hi,

Since each day at your sample data defines 8 hours, please use div function for example:

load

     id, div(WorkingHours,8)+1 as Days

from ....

Thanks!

[#5]

hi,

if( WorkingHours <=8,'1 Day',

         if( WorkingHours <=16,'2 Days',

                if(WorkingHours <=25,'3 Days'))) as Day_Bucket,

Regards

Vimlesh

[#6]

Hi Amay,

I dont need all the values in one field. I need to make different fields like 1 day. 2 days. 3 days.

IWhen I try like :

if([Processing Period] <=1,[Processing Period]) as [1 hour]

It gives 1 and other values that are bigger than 1

[#7]

Guys, guys ,guys,

I need this values in different fields not in 1 field.

As I said before, somehow when i write If ([Working hours] <=1) it gives me a lot of values that are bigger than 1

[#8]

Hi Diana,

Try like this:

If....., Dual('1-8 hours', 1),

If(...., Dual('9-16 hours', 2)... and so on

[jagan · Accepted solution]

Hi,

Try like this

LOAD

*,

if(WorkingHours > 0 AND WorkingHours <=8,'1 Day') AS 1Day,

if(WorkingHours > 8 AND WorkingHours <=16,'1 Day') AS 2Days,

if(WorkingHours > 16 AND WorkingHours<=25,'3 Days') AS 3Days,

If(WorkingHours > 25, '>3Days') AS GT3Days

FROM DataSource;

Regards,

Jagan.

[robert_mika]

t1:

load * inline

[

Numbers

1

2

3

4

5

6

7

11

5

6

3

22

6

3

]

;

Day1:

load if(Numbers<=8,Numbers) as Day1,

if(Numbers>8 and Numbers <=16,Numbers) as Day2,

if(Numbers>17 and Numbers <=25,Numbers) as Day3

resident t1;

drop table t1