topic Re: Conditional Load SQL in QlikView

Conditional Load SQL

amit_saini — Mon, 19 Nov 2018 11:00:30 GMT

Hello Folks,

I'm using below simple script :

SQL SELECT *
FROM "TEST".dbo.CAQ
where [Date] > '2018-11-19 09:46:00' AND [Date] <= '2018-11-19 9:47:00';

What I'm looking for it to set a dynamic where condition , I just want to load the data between Max(Date) timestamp with a range of 1 minute.

[Date] > '2018-11-12 09:46:00' AND [Date] <= '2018-11-12 09:47:00';

How can I set this where condition , please help!

Regards,

Re: Conditional Load SQL

Or — Mon, 19 Nov 2018 11:45:38 GMT

This doesn't appear a Qlik question.. you're seemingly trying to change your SQL, as I don't see any load statements. Looks like SQL Server syntax.

MAX(date) OVER (PARTITION BY group) will get the max date. DateAdd(minute,-1,date) can be used to remove a minute.

Re: Conditional Load SQL

marcus_malinow — Mon, 19 Nov 2018 11:50:16 GMT

You could try something like the following to loop through a days worth of minutes

Include a SQL call where the trace statement is

LET vDate= Makedate(2018, 11, 11) * 1440; //date, expressed in minutes
LET vDateEnd = vDate + 1440; //end date, expressed in minutes
let vPreviousTimeStamp = Timestamp(vDate/1440);
for n = vDate to vDateEnd
     let vCurrentTimeStamp = Timestamp((n+1)/1440);
     TRACE $(vPreviousTimeStamp) to $(vCurrentTimeStamp);
      //put your sql statement here, substituting in $(vPreviousTimeStamp) and $(vCurrentTimeStamp)
      let vPreviousTimeStamp = vCurrentTimeStamp;
next n

Re: Conditional Load SQL

sunny_talwar — Mon, 19 Nov 2018 12:05:08 GMT

May be something like this

SQL SELECT *
FROM "TEST".dbo.CAQ t1,
(SELECT Max(Date) as Max,
   Max(Date) - (1/1440) as Min
FROM "TEST".dbo.CAQ) t2
Where [t1.Date] > t2.Min AND [Date] <= t2.Max;

Re: Conditional Load SQL

pablolabbe — Mon, 19 Nov 2018 12:50:38 GMT

Hi,

Please remember to post questions related to the product in the correct discussion board.

Here is the list of discussion board for qlik products, https://community.qlik.com/t5/Qlik-Products/ct-p/qlik-products

This post will be moved to the correct discussion board soon.

Thanks, Pablo

Re: Conditional Load SQL

amit_saini — Mon, 19 Nov 2018 15:21:33 GMT

Hi,

I'm using SQL Statement in QVW , without load statement also this works.

Regards,

Re: Conditional Load SQL

Or — Mon, 19 Nov 2018 15:59:41 GMT

@amit_saini wrote:
Hi,
I'm using SQL Statement in QVW , without load statement also this works.
Regards,
AS

Yes, but the point is - you're not actually asking anything related to Qlik. Your question is how to modify an SQL query to return specific data.

In any case, the solution I suggested above should work if you're working with Microsoft SQL Server. Since SQL syntax is provider-specific, it may not work for other DBs.