Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter to only display todate as XMMDD

I am needing a way to filter data by date.

Currently trying

Let lastrefresh = now ();

XMMDD

From this I need to create a filter to filter to equal X1212.

tomorrow it should equal x1213

1 Solution

Accepted Solutions
sunny_talwar

So its not a x, but a # in front of the date?

May be this:

LET vFilter = '#' & Date(Today(), 'MMDD');


LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';


LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM Rx.dbo.Rxs

Where RxBatch = '$(vFilter)';

View solution in original post

7 Replies
sunny_talwar

May be this:

'x' & Date(Today(), 'MMDD')

Not applicable
Author

This is were I have it but not sure whee to put the statement you gave me?

SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';

Let lastrefresh = now ();

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

sunny_talwar

What is your date field in the above script you have posted?

Not applicable
Author

I am not trying to match the date field. we name our RxBatch X1212  the 1212 is our naming convention we use.

The last refresh part is only to get to the MMDD

No really sure how the or where the Let lastrefresh = now();

This report refreshes every 5 minutes.

If we could only pull in RXbatch XMMDD would be great or use it as a filter pane on the sheet would be ok also.

sunny_talwar

I am not sure what you are looking to do, but just guessing...

LET vFilter = 'x' & Date(Today(), 'MMDD');

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM ....

Where RxBatch = '$(vFilter)';

Not applicable
Author

When I run this, it does not load any data.

LET vFilter = 'x' & Date(Today(), 'MMDD');


LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';


LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM Rx.dbo.Rxs

Where RxBatch = '$(vFilter)';

This is what my data looks like.  I am trying to out everything but the #MMDD

So the ones in the red circle should be the only thing being pulled.

sunny_talwar

So its not a x, but a # in front of the date?

May be this:

LET vFilter = '#' & Date(Today(), 'MMDD');


LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';


LOAD FacID,
PSID,
    RxBatch,
    RxNo,
    DispenseDt,

FROM Rx.dbo.Rxs

Where RxBatch = '$(vFilter)';