topic Re: Lean SQLing ;-) in QlikView

Lean SQLing ;-)

datanibbler — Thu, 17 Jul 2014 11:34:43 GMT

Hi,

is there any way I can make this rather complex expression (within a LOAD statement) simpler?

if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'0730','07:30',
if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1000','10:00',
   if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1200','12:00',
    if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1400','14:00',
     if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1600','16:00',
      if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1830','18:30',
        if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'2030','20:30',
          if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'2230','22:30','22:30+')))))))) as Interval_2H,

The difficult thing is that these are actually not regular 2hr-intervals as the name suggests - see the 30min delay between 7:30 am and 10am and the one between 4pm and 6:30pm - so I cannot use the BUCKET method proposed by Henric Cronstroem, can I?

I will try - but that is an independent thing - to store the times in some central place and just load them into the app as variables.

First, however, I would like to get all this a bit leaner and more elegant since this is part of a report that is ultimately supposed to run every 10min.

Thanks a lot!

Best regards,

DataNibbler

Re: Lean SQLing ;-)

marcus_malinow — Thu, 17 Jul 2014 11:48:47 GMT

I'd possibly do this using an inline load for the intervals, and an intervalmatch.

For example

Intervals:

LOAD * INLINE [

From, To, InterValName

00:00, 07:30, 07:30

etc...

];

INTERVALMATCH (END_TIME) LOAD From, To RESIDENT Intervals;

Re: Lean SQLing ;-)

hic — Thu, 17 Jul 2014 11:59:41 GMT

I would also use IntervalMatch.

But why do you use concatenation of hour and minute? The expression

If(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'0730','07:30')

can be written

If(END_TIME<'07:30','07:30')

which simplifies your expression a lot...

HIC

Re: Lean SQLing ;-)

datanibbler — Thu, 17 Jul 2014 12:24:05 GMT

Thanks!

That is just the sort of thing I was looking for.

I cannot test it right now, we're experiencing major server troubles today, but I will edit the code accordingly. Every little helps in this case.

Best regards,

DataNibbler

Re: Lean SQLing ;-)

marcus_malinow — Thu, 17 Jul 2014 13:34:26 GMT

Really don't know why I bother sometimes...

Re: Lean SQLing ;-)

datanibbler — Thu, 17 Jul 2014 13:38:49 GMT

Might be because you're one of the nice guys 😉

That's how it is - in other online Communities you spend some time waiting for any answers, here it's a race for who answers first 😉