topic Re: Expression help in QlikView

Expression help

Tue, 23 Sep 2014 19:30:27 GMT

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.

Re: Expression help

Anonymous — Tue, 23 Sep 2014 19:36:35 GMT

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...

Re: Expression help

Anonymous — Tue, 23 Sep 2014 19:37:27 GMT

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

Re: Expression help

Tue, 23 Sep 2014 20:27:21 GMT

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

Re: Expression help

Anonymous — Tue, 23 Sep 2014 21:41:18 GMT

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)

Re: Expression help

Tue, 23 Sep 2014 23:04:30 GMT

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

Re: Expression help

JonnyPoole — Tue, 23 Sep 2014 23:19:36 GMT

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

Re: Expression help

Anonymous — Wed, 24 Sep 2014 11:38:00 GMT

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..