Expression help

[#1]

Hello

In my QlikView application, I am bringing in snapshot data which is the 10th of every month. In my SQL, I am bringing in only data for that particular day using the where clause below:

date = to_date('10-'||to_char(sysdate,'mon-rrrr'),'dd-mon-rrrr')

However, this does not work and has no records when it is before the 10th of the current month. I am looking for a QlikView solution to fix this. Is there any expression that I could use to bring in data for the 10th of the previous month if it is before the 10th of the current month?

Thanks.

[Anonymous]

if i am in your place, i would use peek function.

OLDDataQVD:

Load max(date) as maxdate

from oldQVD;

let vAsOfdate       = peek('maxdate',0,'OLDDataQVD');

newdata:

load * ;

sql select * from table

where date>=$(vAsOfdate)

;

Hope this helps...

[Anonymous]

if there is any sample app , we can be able to help you .

[#4]

This data has no QVD. It runs once a month and brings in data for that date only.

[Anonymous · Accepted solution]

use this script while loading the qvd .. hope this helps...if still have any issue. Post some data in excel sheet .. i can help you ...

let vAsOfdate       = makedate(year(today()),month(today()),10);

newdata:

load * ;

sql select * from table

where date=$(vAsOfdate)

[#6]

Hi Vijay, this is no QVD, only script in QlikVIew. Would this still work?

[JonnyPoole]

You can use an IF statement in the script to change the LOAD depending on the day of the month .

Here is an ALL QlikView example. In your case, you could do the where filter in your SQL:

-------------

if day(Today()) < 10 then

LOAD 

  Date,

  Month(Date) as Month,

  Day(Date) as Day

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where Day(Date) = 10 and  month(Date)= month(AddMonths(Today(),-1))

;

ELSE

LOAD

  Date,

  Month(Date) as Month,

  Day(Date) as Day

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where Day(Date) = 10 and Month(Date)=Month(Today());

ENDIF

[Anonymous]

HI R S ,

Yes it would still work...if you see my new data it is pulling from SQL table. All we are doing here is declaring a variable whose value is a date every 10th of every current month ..After that we are pulling the data from database using that variable in the where clause...Please let me know if this helps......

Another suggestion ,  if this is your situation  every 10th new is loaded into the table and old data is erased then ...write the script to pull the information from table. And schedule the application in publisher on every 10th of the month ..then your problem is solved..