Load data which is less then today()

[bushpalaamarnat]

Hi, could any one help. 

I have been trying this for past 2 weeks now in different way. am trying to create a qvd file based on date, by function week start date. 

let vDateStamp = Text(date(today(), 'DD-MM-YYYY'));
let vDateStamp2 = Text(WeekStart((today()-46), 'DD-MM-YYYY'));

Weekload:

LOAD *,
//date(MakeWeekDate((2000+right(Week,2)),mid(Week,2,2),0), 'MM/DD/YYYY')+ 46 as WeekStart_Date2,
Date#(MakeWeekDate((2000+right(Week,2)),mid(Week,2,2),0), 'DD-MM-YYYY')as WeekStart_Date

FROM
[\\uk2dsntv001.corpnet2.com\GMS_SCLDesign\Work In Progress\Respiratory\Neelima Bushpala-BPS\Work Package\Inventory Corridor Dashboard\Output QVD\ICR_Load.qvd]
(qvd)
;

ICRLoad:
NoConcatenate
load
*,
Text(Date(WeekStart_Date,'DD-MM-YYYY')) as WeekStart

Resident Weekload;

ICR:
NoConcatenate
load
*
Resident ICRLoad
Where ("WeekStart" <= $(vDateStamp) )
and ("WeekStart" >= $(vDateStamp2))
;

STORE ICR into \\uk2dsntv001.corpnet2.com\GMS_SCLDesign\Work In Progress\Respiratory\Neelima Bushpala-BPS\Work Package\Inventory Corridor Dashboard\Output QVD\History.qvd(qvd);

Drop Tables Weekload;
Drop Tables ICRLoad;

[Vegar]

My first concern would be using text(date(...)) both in the LOAD and in the variables. I would skip the text() and only used Date(...) hence you are trying to do numeric comparisons between the field and variables at the end.

[pradosh_thakur]

remove the text() befoe the dates in the variable as wellas in the week start

[pradosh_thakur]

let vDateStamp = date(today(), 'DD-MM-YYYY');
let vDateStamp2 = WeekStart((today()-46), 'DD-MM-YYYY');

Weekload:

LOAD *,
//date(MakeWeekDate((2000+right(Week,2)),mid(Week,2,2),0), 'MM/DD/YYYY')+ 46 as WeekStart_Date2,
Date#(MakeWeekDate((2000+right(Week,2)),mid(Week,2,2),0), 'DD-MM-YYYY')as WeekStart_Date

FROM
[\\uk2dsntv001.corpnet2.com\GMS_SCLDesign\Work In Progress\Respiratory\Neelima Bushpala-BPS\Work Package\Inventory Corridor Dashboard\Output QVD\ICR_Load.qvd]
(qvd)
;

ICRLoad:
NoConcatenate
load
*,
Date(WeekStart_Date,'DD-MM-YYYY') as WeekStart

Resident Weekload;

ICR:
NoConcatenate
load
*
Resident ICRLoad
Where ("WeekStart" <= $(vDateStamp) )
and ("WeekStart" >= $(vDateStamp2))
;

STORE ICR into \\uk2dsntv001.corpnet2.com\GMS_SCLDesign\Work In Progress\Respiratory\Neelima Bushpala-BPS\Work Package\Inventory Corridor Dashboard\Output QVD\History.qvd(qvd);

Drop Tables Weekload;
Drop Tables ICRLoad;

[bushpalaamarnat]

I tried with out text and still nothing is loading

[pradosh_thakur]

change this and try
let vDateStamp2 = WeekStart(date(today()-46, 'DD-MM-YYYY'));

[bushpalaamarnat]

In the data set there is no date field, have to create a date field using Week coloum hence used week start date. 

removed text also it did not work. 

The week is based on this column  W06-19 create a date and load only those data which is less then or equal to today. 

The data set has history, current and future data 

[bushpalaamarnat]

In the data set there is no date field, have to create a date field using Week column hence used week start date. 

removed text also it did not work. 

The week is based on this column  'W06-19' create a date and load only those data which is less then or equal to today. 

The data set has history, current and future data 

[pradosh_thakur]

let vDateStamp2 = WeekStart(date(today()-46, 'DD-MM-YYYY'));

and make sure your date is a date and nut a text. check this bu using num(date) in filter or max(date ) in text box

[bushpalaamarnat]

I tired as suggested with let vDateStamp2 = WeekStart(date(today()-46), 'DD-MM-YYYY');