Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help regarding convert SQL Query to QlikView query

I want to create qlikview query same as below sql query . Please suggest.

DECLARE @gh AS TABLE (JN BIGINT,FT NVARCHAR(50),Ptime DATETIME)

INSERT INTO @gh SELECT 1,'CB2','12:30'
Union
SELECT 2,'CB2','12:37'
Union
SELECT 3,'CB2','13:45'
Union
SELECT 4,'CB1','12:37'
Union
SELECT 5,'CB1','12:40'
Union
SELECT 6,'CB2','13:40'

SELECT gh1.JN,gh1.FT,gh1.Ptime,COALESCE(gh2.jn,gh3.jn) as OtheFT, COALESCE(gh2.Ptime,gh3.Ptime) AS OtherPtime--,gh3.Ptime , DATEADD(MINUTE,30,gh1.Ptime),gh1.Ptime
FROM @gh gh1 Left Outer Join @gh gh2 on (gh1.FT=gh2.FT AND gh1.JN<>gh2.JN AND gh2.Ptime >= DATEADD(MINUTE,-30,gh1.Ptime) AND gh2.Ptime <= gh1.Ptime)
    Left Outer Join @gh gh3 on (gh1.FT=gh3.FT AND gh1.JN<>gh3.JN AND gh3.Ptime <= DATEADD(MINUTE,30,gh1.Ptime) AND gh3.Ptime >= gh1.Ptime)  

Tags (1)