Age Bracket (Delay) not arrayed in an ascending order

[nevilledhamsiri]

Hi,

As per the data loaded, Delay age brackets are not arranged in an ascending order. I have don the sorting in ascending order including expression arranged through Min function but ages are not in an order. Please look in to this.

Regards

Neville

[pradosh_thakur]

I cant see you script and how you calculated delay_flag. I will suggest you use dual()function.

something like instead of 0-30 it should be dual('0-30',1) then instead of 30-60 it should be dual('30-60',2) and so on.

let me know if this works

[nevilledhamsiri]

Dear Pradosh,

If I add the given sequence as you suggested an error comes. Could you see how this is done, Below is my script age. This of course did work for other cases when I applied. Please re write it if ossible

Regds

Neville

MONTH(LOSS_DATE)AS LOSS_MONTH,

     IF(( REPORTED_DATE-LOSS_DATE)>150,'>150',

     IF(( REPORTED_DATE-LOSS_DATE)>120,'>120',

     IF((REPORTED_DATE-LOSS_DATE)>90,'>90',

     IF((REPORTED_DATE-LOSS_DATE)>60,'60-90',

     IF((REPORTED_DATE-LOSS_DATE)>30,'30-60','0-30'))))) AS DELAY_FLAG,

[pradosh_thakur]

MONTH(LOSS_DATE)AS LOSS_MONTH,

     IF(( REPORTED_DATE-LOSS_DATE)>150,dual('>150',6),

     IF(( REPORTED_DATE-LOSS_DATE)>120,dual('>120',5),

     IF((REPORTED_DATE-LOSS_DATE)>90,dual('>90',4),

     IF((REPORTED_DATE-LOSS_DATE)>60,dual('60-90',3),

     IF((REPORTED_DATE-LOSS_DATE)>30,dual('30-60',2),dual('0-30',1)))))) AS DELAY_FLAG,

[pradosh_thakur · Accepted solution]

or

Check for syntax error once as i have typed it in the browser.

MONTH(LOSS_DATE)AS LOSS_MONTH,

pick(match(1,( REPORTED_DATE-LOSS_DATE)>150,( REPORTED_DATE-LOSS_DATE)>120,( REPORTED_DATE-LOSS_DATE)>90,( REPORTED_DATE-LOSS_DATE)>60,( REPORTED_DATE-LOSS_DATE)>30)+1,dual('0-30',1)

,dual('>150',6),dual('>120',5),dual('>90',4),dual('60-90',3),dual('30-60',2)) AS DELAY_FLAG