topic Getting only todat's date value from database in QlikView

Getting only todat's date value from database

ajaykumar1 — Fri, 30 Oct 2015 09:36:31 GMT

Hi Everyone,

I have the fields like below.

WHAT	STATE	STARTED	FINISHED
First Job	Ready	10/29/2015 9:01	10/29/2015 9:01
First Job	Ready	10/28/2015 8:01	10/28/2015 8:40
First Job	Ready	10/27/2015 8:01	10/27/2015 8:28
First Job	Ready	10/25/2015 7:16	10/25/2015 7:35
First Job	Ready	10/23/2015 10:01	10/23/2015 10:48
First Job	Not Ready	10/29/2015 9:00	10/29/2015 9:01
First Job	Process	10/29/2015 9:00	10/29/2015 9:01
First Job	Middle	10/29/2015 9:00	10/29/2015 9:01

My conditions when am getting the data from the data base is;

What = 'First Job' and State = Ready and Started = todaysdate

With that above sample data then 0 records should come .But after if i run this extractor after some time

If my 'Started' have todays time stamp(i.e. e.g 10/30/2015 10.15) then i need to get those records.

I hope my requirement is clear.

Thanks,

Ajay

Re: Getting only todat's date value from database

Anonymous — Fri, 30 Oct 2015 09:43:35 GMT

you already specified your conditions.

use the follwoing condition

load ...

from xyz

where (What='First Job') and (State='Ready') and (date#(Started,'DD/MM/YYYY')=today())

maybe you Need to Format today() also if today() doesnot return in Format DD/MM/YYYY

Re: Getting only todat's date value from database

mayankraoka — Fri, 30 Oct 2015 09:45:14 GMT

Hi,

USE TODAY() to get latest date

Regards,

Mayank

Re: Getting only todat's date value from database

ajaykumar1 — Fri, 30 Oct 2015 09:54:06 GMT

Thanks Rudolf.But its not working and giving error related to today

(What='First Job') and (State='Ready') and (date#(Started,'DD/MM/YYYY')=today('DD/MM/YYYY')) ---today is invalid identifier

Am getting data from DB...its looks like some time stamp issue..

Plz attach the sample app if any.

Ajay

Re: Getting only todat's date value from database

HirisH_V7 — Fri, 30 Oct 2015 10:04:40 GMT

Hi ajay ,

Check the attachment 1 & 2.Hope it helps.

thanks,

Hirish

Re: Getting only todat's date value from database

Anonymous — Fri, 30 Oct 2015 10:05:26 GMT

today is only valid as function today()

if you Need further formatting you may use

date function around today()

date#(today(),'DD/MM/YYYY') - means, recognizes the date Format of today() which is in Format DD/MM/YYYY

-> If you know that today() Returns this Format you donot Need the data# function

-> try in TextBox =today() to see what dateformat will be returned

Re: Getting only todat's date value from database

ajaykumar1 — Fri, 30 Oct 2015 10:17:17 GMT

Hi Harish,

I want to from backend.

Re: Getting only todat's date value from database

ajaykumar1 — Fri, 30 Oct 2015 10:18:59 GMT

I have tried that...But not working...

Could you please add my logic in sample data which i given..

More thanks,

Ajay

Re: Getting only todat's date value from database

HirisH_V7 — Fri, 30 Oct 2015 10:22:37 GMT

Check the Second attachment . Make mods in time stamp according to your requirement .

=Today('DD-MM-YY h:mm:ss[.fff] TT')

Hope that helps,

Regards,

Hirish

Re: Getting only todat's date value from database

Anonymous — Fri, 30 Oct 2015 10:28:43 GMT

i assume, that today() is not recognized from your database

can you check what function is available from your database

alternative you load without restriction of date and provide

the condition in QV while loading from resident table

if you get a uge amount of data, best solution let the database

do the conditions

Step1:

load ..

from ...

where (What='First Job') and (State='Ready')

Table:

noconcatenate load

resident Step1

where (date#(Started,'DD(date#(Started,'DD/MM/YYYY')=today())p/MM/YYYY')=today())

drop table Step1

Re: Getting only todat's date value from database

avinashelite — Fri, 30 Oct 2015 10:40:22 GMT

what is your back end database?? if you can specific that we will able to give the exact solution

Re: Getting only todat's date value from database

ajaykumar1 — Fri, 30 Oct 2015 10:46:54 GMT

My backend data base is oracle.

Re: Getting only todat's date value from database

Anonymous — Fri, 30 Oct 2015 10:54:53 GMT

then try with CurDate() (

load ...

from xyz

where (What='First Job') and (State='Ready') and (date#(Started,'DD/MM/YYYY')=CURDATE())

Oracle Spec

Table 12.13 Date/Time Functions

Name	Description
`ADDDATE()`	Add time values (intervals) to a date value
`ADDTIME()`	Add time
`CONVERT_TZ()`	Convert from one timezone to another
`CURDATE()`	Return the current date

Re: Getting only todat's date value from database

ajaykumar1 — Fri, 30 Oct 2015 11:01:14 GMT

Hi All,

I have tried all those things like systime,curdate() and today() and etc..

But am unable to get the positive reslut...Getting the proper with with whatever the same data which i provided earlier with same time stamp...thats good.

Thanks for all your patience support for my small work.:-)

Ajay

Re: Getting only todat's date value from database

maxgro — Fri, 30 Oct 2015 12:47:06 GMT

in Oracle use

SYSDATE --> 30/10/2015 13:45:58

or trunc(SYSDATE) --> 30/10/2015

an example, adapt to your req

load

.....

;

SQL

SELECT

.........

WHERE STARTED >= SYSDATE;

Re: Getting only todat's date value from database

vishsaggi — Fri, 30 Oct 2015 13:58:55 GMT

Hello AJay,

Probably first get the data from Oracle and load into a temp table and

do a resident load as given by Rudolf. Did you try that resident load???

Re: Getting only todat's date value from database

tschullo — Fri, 30 Oct 2015 14:40:46 GMT

WHERE DATE(STARTED) = Today();