topic Re: Load only from perticular month in QlikView

Load only from perticular month

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Load only from perticular month

sudhirpkuwar — Fri, 15 Jun 2018 10:42:42 GMT

SQL Select * from

where createdDate>=5/1/2018

Re: Load only from perticular month

jaumecf23 — Fri, 15 Jun 2018 10:44:40 GMT

Hi.

try something like this :

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

Re: Load only from perticular month

soniasweety — Fri, 15 Jun 2018 10:47:34 GMT

but I need dynamic

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

Re: Load only from perticular month

dapostolopoylos — Fri, 15 Jun 2018 10:49:41 GMT

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

Re: Load only from perticular month

sudhirpkuwar — Fri, 15 Jun 2018 10:50:08 GMT

can you be more specific

Dynamic means what...?

Re: Load only from perticular month

soniasweety — Fri, 15 Jun 2018 10:59:45 GMT

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

Re: Load only from perticular month

wilsonwebb — Fri, 15 Jun 2018 12:25:04 GMT

Load

* ;

SQL Select

FROM TableName

Where createdDate > '4/30/2018' ;

Re: Load only from perticular month

Colin-Albert — Fri, 15 Jun 2018 12:44:15 GMT

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.

Re: Load only from perticular month

soniasweety — Fri, 15 Jun 2018 12:49:10 GMT

its not working giving

today is not sql server function

Re: Load only from perticular month

Colin-Albert — Fri, 15 Jun 2018 12:50:32 GMT

The functions are all QlikView functions.

These will be applied in the qlikview load script before the query is passed to the SQL engine.

Re: Load only from perticular month

sudhirpkuwar — Fri, 15 Jun 2018 12:50:34 GMT

What exactly you want

Re: Load only from perticular month

soniasweety — Fri, 15 Jun 2018 12:59:52 GMT

am loading the table from data base to qlik

LOAD ID,

CreatedDate;

sql select

ID,

CreatedDate;

here I need to write the where condtion to get the data >April Month 2018

Re: Load only from perticular month

Colin-Albert — Fri, 15 Jun 2018 13:07:36 GMT

Try this

LOAD ID,

CreatedDate;

sql select

ID,

CreatedDate

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

;

Re: Load only from perticular month

wilsonwebb — Fri, 15 Jun 2018 13:07:45 GMT

Is this what you need Sony? Please be mindful of where you put your semicolon.

LOAD ID,
CreatedDate;
sql select
ID,
CreatedDate

FROM DBTABLE

WHERE CreatedDate > '4/30/2018';

Re: Load only from perticular month

Colin-Albert — Fri, 15 Jun 2018 13:19:16 GMT

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)'

;