topic Re: Urgent : Get the value based on hours in QlikView

Urgent : Get the value based on hours

n1ef5ng1 — Mon, 12 Feb 2018 07:19:53 GMT

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

Re: Urgent : Get the value based on hours

tresB — Mon, 12 Feb 2018 07:35:01 GMT

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

Re: Urgent : Get the value based on hours

n1ef5ng1 — Mon, 12 Feb 2018 07:36:29 GMT

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

Re: Urgent : Get the value based on hours

n1ef5ng1 — Mon, 12 Feb 2018 07:41:52 GMT

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

Re: Urgent : Get the value based on hours

tresB — Mon, 12 Feb 2018 07:47:03 GMT

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?

Re: Urgent : Get the value based on hours

n1ef5ng1 — Mon, 12 Feb 2018 07:51:26 GMT

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

Re: Urgent : Get the value based on hours

tresB — Mon, 12 Feb 2018 08:58:33 GMT

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;

Re: Urgent : Get the value based on hours

n1ef5ng1 — Mon, 12 Feb 2018 09:11:58 GMT

Thanks alot! is correct