Simulating a logic with Row Number Over and Left Join

[bilalgunay]

Dear All,

I have a logic that is developed in SQL and I am trying to simulate/calculate it in Qlik. As I am relatively new in Qlik, I could not apply the bits and pieces correctly, thus need your help.

The SQL Code you can find below:

select Application, Location, Time, case when min(STP) = 0 then 'YES' else 'NO' end FLAG_6HR

from (

select distinct Application, Location, Time, sum(Availability) as STP

from

(

select BASIS1.Application, BASIS1.Location, BASIS2.Time, BASIS1.Availability

from

(

select Application, Location, Time, Availability

, row_number() OVER(PARTITION BY 1 ORDER BY Application, Location, Time)   as RW_NR

from Table

) BASIS1

left join

(

select Application, Location, Time, Availability

, row_number() OVER(PARTITION BY 1 ORDER BY Application, Location, Time)   as RW_NR

from Table

) BASIS2

on BASIS1.Application = BASIS2.Application

  and BASIS1.Location = BASIS2.Location

  and BASIS2.RW_NR between BASIS1.RW_NR - 11 and BASIS1.RW_NR

) as TOT

GROUP by Application, Location, Time

) FINAL

There is also an example Excel attached. I created the Excel only for a day, an application and one location though. The solution should be more dynamic for those dimensions.

I receive an availability result for some applications and some locations every 30 min. If the availability is 0 for consequtive 6 hours the aggrement is then breached thus the flag is calculated in Excel. Mind that the this Information is not limited to a day thus the 6 hours window is always sliding.

Could anyone help me on this?

Please do not hesitate to ask questions if it is not clear enough.

[sunny_talwar]

Is the goal of this thread is to calculate the Flag_6HR within Qlikview rather than doing this in SQL?

[bilalgunay]

Hey Sunny,

Yes you could say that. Though this is not the final goal of my calculation, I hope I can manage from there onwards.

I have some 7 Different Applications and 16 Different Locations for which I receive the Availability every 30 minute intervals.

And as a part of the KPI, I need to calculate this flag as it is crucial if it is breached or not.

[sunny_talwar · Accepted solution]

This can be one way to do it

Table:

LOAD *,

TimeStamp(Today() + Time) as DateTime;

LOAD Time,

    Application,

    Location,

    Availability

FROM

[..\..\Downloads\Example.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If(

RangeSum(

Availability,

If(Application = Peek('Application', -1) and Location = Peek('Location', -1), Peek('Availability', -1), 0),

If(Application = Peek('Application', -2) and Location = Peek('Location', -2), Peek('Availability', -2), 0),

If(Application = Peek('Application', -3) and Location = Peek('Location', -3), Peek('Availability', -3), 0),

If(Application = Peek('Application', -4) and Location = Peek('Location', -4), Peek('Availability', -4), 0),

If(Application = Peek('Application', -5) and Location = Peek('Location', -5), Peek('Availability', -5), 0),

If(Application = Peek('Application', -6) and Location = Peek('Location', -6), Peek('Availability', -6), 0),

If(Application = Peek('Application', -7) and Location = Peek('Location', -7), Peek('Availability', -7), 0),

If(Application = Peek('Application', -8) and Location = Peek('Location', -8), Peek('Availability', -8), 0),

If(Application = Peek('Application', -9) and Location = Peek('Location', -9), Peek('Availability', -9), 0),

If(Application = Peek('Application', -10) and Location = Peek('Location', -10), Peek('Availability', -10), 0),

If(Application = Peek('Application', -11) and Location = Peek('Location', -11), Peek('Availability', -11), 0)) = 0, 'YES', 'NO') as Flag_6HR

Resident Table

Order By Application, Location, DateTime desc;

DROP Table Table;

[bilalgunay]

Hey Sunny,

in between I was partly out of office and then tested it. Sorry for the late response, but it worked. Thanks alot!