Expression conversion to Set Analysis

[Broly]

Hello all,

I would like to ask you whether it is possible to change this expression to a set analysis? Also, would it be possible maybe to change it to a load script?

I need this to put it to a KPI object.

time(if(AT_14_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_14) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_14) < num(date(today())+ 0.5))) ,sum(AT_14_TIME_DIFF),0)+
if(AT_1_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_1) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_1) < num(date(today())+ 0.5))),sum(AT_1_TIME_DIFF),0) +
if(AT_2_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_2) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_2) < num(date(today())+ 0.5))),sum(AT_2_TIME_DIFF),0) +
if(AT_3_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_3) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_3) < num(date(today())+ 0.5))),sum(AT_3_TIME_DIFF),0) +
if(AT_4_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_4) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_4) < num(date(today())+ 0.5))),sum(AT_4_TIME_DIFF),0) +
if(AT_5_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_5) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_5) < num(date(today())+ 0.5))),sum(AT_5_TIME_DIFF),0) +
if(AT_6_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_6) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_6) < num(date(today())+ 0.5))),sum(AT_6_TIME_DIFF),0) +
if(AT_7_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_7) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_7) < num(date(today())+ 0.5))),sum(AT_7_TIME_DIFF),0) +
if(AT_8_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_8) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_8) < num(date(today())+ 0.5))),sum(AT_8_TIME_DIFF),0) +
if(AT_9_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_9) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_9) < num(date(today())+ 0.5))),sum(AT_9_TIME_DIFF),0) +
if(AT_10_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_10) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_10) < num(date(today())+ 0.5))),sum(AT_10_TIME_DIFF),0) +
if(AT_11_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_11) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_11) < num(date(today())+ 0.5))),sum(AT_11_TIME_DIFF),0) +
if(AT_12_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_12) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_12) < num(date(today())+ 0.5))),sum(AT_12_TIME_DIFF),0) +
if(AT_13_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_13) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_13) < num(date(today())+ 0.5))),sum(AT_13_TIME_DIFF),0)

,'hh:mm:ss')

[Kushal_Chawda]

It will be better to move it to load script with below approach

create a Field in script like below/ I have covered only two condition for example but you need to include all conditions. You can include '0' in else  for last if statement.

LOAD *,

if(AT_14_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_14) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_14) < num(date(today())+ 0.5))),AT_14_TIME_DIFF,

if(.....,

If(......,

if(AT_1_TIME_DIFF_CAT>0 and ((num(MAPPED_AT_1) > num(date(today())+ 0.27083333)) and (num(MAPPED_AT_1) < num(date(today())+ 0.5))),AT_1_TIME_DIFF,0)) as Total_Time

FROM Source;

Now you can simply use below formula 

=time(sum(Total_Time))

[Broly · Accepted solution]

This is what I came up with

(if(DT_0_TIME_DIFF_CAT>0,1,0) + if(DT_1_TIME_DIFF_CAT>0,1,0) + if(DT_2_TIME_DIFF_CAT>0,1,0) +if(DT_3_TIME_DIFF_CAT>0,1,0) + if(DT_4_TIME_DIFF_CAT>0,1,0) +if(DT_5_TIME_DIFF_CAT>0,1,0) + if(DT_6_TIME_DIFF_CAT>0,1,0) + if(DT_7_TIME_DIFF_CAT>0,1,0) + if(DT_8_TIME_DIFF_CAT>0,1,0) + if(DT_9_TIME_DIFF_CAT>0,1,0) + if(DT_10_TIME_DIFF_CAT>0,1,0) + if(DT_11_TIME_DIFF_CAT>0,1,0) + if(DT_12_TIME_DIFF_CAT>0,1,0) + if(DT_13_TIME_DIFF_CAT>0,1,0)) as #DepartureDelay,