Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
soniasweety
Valued Contributor III

Load only from perticular month

hi all

i have data from jan 2018 in my data base    but I wan to load only from may 1st -2018

My date

createdDate = 4/30/2018  7:39:24 PM

how to load  only from may?  what is the where condition?

Thanks

Sony

Tags (2)
1 Solution

Accepted Solutions

Re: Load  only from perticular month

This should work by setting the date as a variable CDate formatted as YYYY-MM-DD and using that date in the where clause.

Let CDate = date(monthstart(addmonths(today(), -3)), 'YYYY-MM-DD') ;

LOAD

     ID,

     CreatedDate;

sql select

     ID,

     CreatedDate

where createdDate >= '$(CDate)'

;

15 Replies
Partner
Partner

Re: Load only from perticular month

SQL Select * from

where createdDate>=5/1/2018

jaumecf23
Contributor III

Re: Load only from perticular month

Hi.

try something like this :

where num(Month(Date#(createdDate, 'MM/DD/YYYY HH:mm:ss TT')))=5

soniasweety
Valued Contributor III

Re: Load only from perticular month

but I need dynamic

and Database where condition I need   because  am writing this condition in qvd generator.

dapostolopoylos
Contributor III

Re: Load only from perticular month

If you want to filter your data in LOAD script then:

LOAD

.....

Where

     Num(Month(createdDate))=5

If you want to filter your data in the db level add this to the where clause of your SQL query:

SELECT

     *

FROM

     Table

WHERE

     MONTH(createdDate)=5

Father/Husband/BI Developer
Partner
Partner

Re: Load only from perticular month

can you be more specific

Dynamic means what...?

soniasweety
Valued Contributor III

Re: Load only from perticular month

it is not dynamic  right?

for example I have 2 yrs data I wil get  both years data  greater than april.   but I need data greater than  April 2018 only

how to do in Sql server Db

Partner
Partner

Re: Load only from perticular month

Load

*   ;

SQL Select

*

FROM TableName

Where createdDate > '4/30/2018' ;

Re: Load only from perticular month

Using this expression in the where clause of the sql part of the QlikView load script, will dynamically load data from the 1st of the month 3 months ago.  The date(floor()) function converts the datetime value monthstart() to a date.

    where createdDate >= date(floor(monthstart(addmonths(today(), -3))))


So    
if today is 2018-06-16  Then the load will use   where createdDate >= 2018-03-01

         if today is 2018-06-17  Then the load will use   where createdDate >= 2018-03-01

         if today is 2018-07-02  Then the load will use   where createdDate >= 2018-04-01


You can change the -3 to whatever value you prefer.

soniasweety
Valued Contributor III

Re: Load only from perticular month

its not working giving

today is not sql server function