only load the data that is created today

[#1]

Helo,

I am new with Qlikview and do a lot a reading through the Qlikview reference manual.

On our SQL db wh have a only want to display the purchase orders that are made today.

I can get them all in to qlikview but I only with the ardes that have a time stamp today.

When i try this I still get all Orders

datumtest:

Load

Datum
where Datum = Today();

I can get the script to only load orders that are created today. Still all orders from this year are loaded. SQL SELECT Datum,
DebiteurID
FROM "Match-Online Test".dbo.Opdracht;

[#2]

Try this..

Main:

SQL SELECTDatum,

DebiteurID
FROM "Match-Online Test".dbo.Opdracht;

LOAD

Datum,

DebiteurID

RESIDENT Main WHERE date(Datum, 'YYYYMMDD') = Today(0);

Drop Table Main;

I suspect your data coming out of your data field doesn't have the right data type to match with today(). We are setting it to be a true date above. May require some adjustments to the date format.

[#3 · Accepted solution]

Thanks Anthony,

this works but now I have 2 Questions.

1. With the command ** drop table main ** still the loaded Datum and DebiteurID are present there not dropped? How come is there a different between a field in the script an in de document?

2. The data in Datum is 5 digit number for time . with the ** Resident Main WHERE date(Datum,'YYYYMMDD') is this done to set te date in the same way qlikview uses it? (like 20090828?)

[#4]

1.)Its because we loaded those fields into a resident table. Essential we just moved it into a table that was easier to work with, then we dropped the original select table.

2.) Qlikview allows you to set the date to any format needed. All the code that I showed you was doing was assuming your date as YYYYMMDD and making the where statement work with that.

Can you give an example of datum so I can help write this for you?

[johnw]

It may be worth noting that this could potentially be VASTLY more efficient if you pass the date check off to the DBMS rather than loading in ALL data from the database, then rejecting anything that isn't from the current date. Untested, but here it is:

LET TODAY = date(today(),'YYYYMMDD')
;
[Datumtest]:
LOAD *
;
SQL SELECT
Datum
,DebiteurID
FROM "Match-Online Test".dbo.Opdracht
WHERE Datum = $(TODAY)
;

In this case, you would need to make sure that TODAY is formatted exactly like it is in your database. As with Anthony's example, I assumed here that your database stores an integer representation of the date in YYYYMMDD format.

[#6]

Hello there,

I do not completly understund what u mean with the Date You are asking for,

I can tell u that the table is design with only integer numbers and is present is an 5 digit number for today it is 40056. I hope that is what u want.

[#7]

Also this question on it (i want to make a dataset to create information over one Month.)

Like Datum from the SQL field is presented as 40056.
I want to make it look like = aug or 8 which is used by Qlikview so when I do

where Datum = month(today())
all data is displayed from that month.
Only now Datum = like 40056
and month(today()) = like aug.

And this doen't match so I have no data.
and everytime i want to read the Datum field from SQL and use date

SQL Select
date(Datum, 'YYYYMMDD')

The system complains that date is not an intergretion SQL function??? ore build in function.
U probably know by now that this is my second week with Qlikview (-:

J.

[johnw]

OK, how is your SQL field formatted? Are you saying that it has the value of 40056 for today, August 31, 2009? If so, great, because that's the same number QlikView uses, which would let you do this:

LET TODAY = num(today());

And you might even be able to get away with this:

LET TODAY = today();

What I'm trying to do is format the QlikView date like the SQL date, and not vice versa.