topic Re: SQL Query in QlikView

SQL Query

Wed, 26 Nov 2014 15:50:48 GMT

Hello Folk,

I am fetching the data from Sql database where i have to fetch data only for Current week and there is a Field called Date where Date is in Format MM/DD/YYYY HH:MM:SS. so what query i have to write for this so that it will only fetch Current Week Data.

Thanx

Re: SQL Query

datanibbler — Wed, 26 Nov 2014 16:15:13 GMT

Hi vini,

I often had that same question, and I ended up going a diffferent way:

Do you know the "preceding LOAD"? I use that every time I have an SQL query - that way I don't have to bother with writing my filters in the SQL_SELECT, but instead I can write them in the LOAD and use the QlikView_syntax.

The preceding LOAD means there is only one pass of the data, not two as with the RESIDENT LOAD, so that's really about as easy as things can get.

HTH

Best regards,

DataNibbler

Re: SQL Query

Wed, 26 Nov 2014 16:25:29 GMT

Can u give me some Sample Query for this..

Re: SQL Query

richard_chilvers — Wed, 26 Nov 2014 16:25:43 GMT

Hi DataNibbler

You make an interesting point.

I think you're suggesting the SQL SELECT gets all the unfiltered data and you use the LOAD to then filter down to what's required in the QV model? My understanding is that this is less efficient than filtering the data you SQL SELECT as this is the time and resource-consuming process?

Anyway, I guess Vini needs a hint about how to use WEEK functions in QV?

Regards

Re: SQL Query

MarcoWedel — Wed, 26 Nov 2014 21:47:15 GMT

Hi,

you could try:

LOAD *
Where InWeek(Date#(Date,'MM/DD/YYYY hh:mm:ss'), ToDay(),0);
SQL SELECT
    SomeFields
FROM YourDBTable;

hope this helps

regards

Marco

Re: SQL Query

MarcoWedel — Wed, 26 Nov 2014 23:48:44 GMT

The prefered solution might be to implement the where clause in the SQL part due to run time issues

Re: SQL Query

jagan — Thu, 27 Nov 2014 03:03:44 GMT

HI,

Fetch current week data by using where condition in SQL itself, it is the best solution.

In SQL Server:

Data:

SELECT

FROM TableName

WHERE DATEPART( wk , DateFieldName) = DATEPART(wk, GetDate());

In Oracle:

Data:

SELECT

FROM TableName

WHERE to_char(DateFieldName,'WW') = to_char(SYSDATE,'WW');

Note: Replace DateFieldName with your actual date field name in table.

If you are not using ORacle or SQL Server then check for corresponding date functions.

Hope this helps you.

Regards,

Jagan.