Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to do
ageing analysis for time and
duration for message like [0-2][2-10][5-20][>20 hrs]
Time duration will be like below
10:06:32 ,
82:05:30 etc
Pls share with me if you ageing analysis like this. Thanks in advance.
Hi.
[0-2][2-10][5-20][>20 hrs]
Do you really use overlapping ranges, or it's a mistake ?
Hi Sorry its typo.
it will be like
[0-2] [3-10][11-20] [>20 hrs]
Thanks
Hi
Try like this
if(hour(timefield) <= 2, '[0-2]',if(hour(timefield) <= 10, '[3-10]', if(hour(timefield)) < = 20,'[11-20]','[>20 hrs]'))) as aging
or use applymap function
Hope it helps
I think this formula is more appropriate:
if(hour(timefield) <= 2, '[0-2]',if(hour(timefield) <= 10 and hour(timefield) > 2 , '[3-10]', if(hour(timefield)) < = 20 and hour(timefield) > 10,'[11-20]','[>20 hrs]'))) as aging
You can't use hour() function if you want to classify periods greater than 24 hours.
div(time*24*60,60) is a good alternative.
In this particular case the calssification can be done without if()s:
=pick(rangemin(div(div(time*24*60,60),3)+1, div(div(time*24*60,60)-1,10)+2, 4), '[0-3)', '[3-11)', '[11-20)', '[>20 hrs]')
I've changed the intervals a little as there are minutes after an hour and I guess you want 2:10 to be classified as [0-2].
Also nulls() are not handled and classified as '[>20 hrs]'.
HI Thanks for your reply. im trying write code as below but my and condtion not working can you pls let me know why ist not working
Load
if([ Time] <= 1 , '[0-1]') ,
if([Time] <= 3 AND if([Time] >=1 ,'[2-3]'),
if([Time] <= 5 , '[4-5]',
if([Time] >= 5, '[5-20]'))) as aging
FROM TAB1;
What are the values of your [Time] field ?
you have a if syntax problem. truy this :
Load
if([ Time] <= 1 , '[0-1]',
if([Time] <= 3 AND if([Time] > 1 ,'[2-3]',
if([Time] <= 5 and [Time] > 4, '[4-5]',
if([Time] > 5, '[5-20]'))) ) as aging
FROM TAB1;
Hi,
Thanks for reply .i have tried its shows as below error
Error in expression:
If takes 2-3 parameters
Thanks