Urgent : Get the value based on hours

[n1ef5ng1]

Hi everyone,

You can refer to the attached for data.

I would like to code in a script on the following. do note that i have to create a table with group by function so i therefore need to use sum of the value.

I would like to come out with a logic to calculate the daily volume based on hour (02 03 04 05 06 07 08). as you can see that there are null values on certain hours, i would like to take the next hour as the final figure.

The figure to get the value is sum(value).

this is my logic

sum(if(BLODR_Hour = '02' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items

if(BLODR_Hour = '03' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items,

if(BLODR_Hour = '04' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items,

if(BLODR_Hour = '05' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items,

if(BLODR_Hour = '06' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items,

if(BLODR_Hour = '07' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items,

if(BLODR_Hour = '08' and not isnull(BLODR_Backlog_DS),BLODR_Total_Items))))))))

however the above code will actually do a summation of 02 - 08 hours. My idea is that on 12.2.18, it should display 6499, 11.2 will be 4032, feb 7th will be 1379. key condition is whichever comes first and have value > 0 , i am stucked in this for almost a day

[tresesco]

You mean, every day should have single value (first >0 value) across all hours?

[n1ef5ng1]

yes, across 2 - 8am. the one with the value > 0 should display first

[n1ef5ng1]

highlighted yellow are the numbers that i would want to display. first

[tresesco]

highlighted yellow are the numbers that i would want to display. first

first? what about rest of the hours? Would a chart solution work for you or you have to do it in the script?

[n1ef5ng1]

ignore the rest of the hours (between 2-8am whichever comes first). must be in script, cant do it in table.

[tresesco · Accepted solution]

Like this?

t1:

CrossTable(Hours, Data)

LOAD Date,

    [02],

    [03],

    [04],

    [05],

    [06],

    [07],

    [08]

FROM

[...\CH01_20180212_151338.xls]

(biff, embedded labels, table is Sheet1$);

Load

      Date,

      FirstSortedValue(if(Data>0, Data), If (Data>0,num#(Hours,'#0'))) as FirstData

Resident t1 Group By Date;

[n1ef5ng1]

Thanks alot! is correct