topic Re: Load data which is less then today() in QlikView

Load data which is less then today()

bushpalaamarnat — Wed, 06 Feb 2019 10:04:18 GMT

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;

Re: Load data which is less then today()

Vegar — Wed, 06 Feb 2019 10:15:54 GMT

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.

Re: Load data which is less then today()

pradosh_thakur — Wed, 06 Feb 2019 10:16:22 GMT

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

Re: Load data which is less then today()

pradosh_thakur — Wed, 06 Feb 2019 10:17:27 GMT

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

Weekload:

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;

Re: Load data which is less then today()

bushpalaamarnat — Wed, 06 Feb 2019 11:21:54 GMT

I tried with out text and still nothing is loading

Re: Load data which is less then today()

pradosh_thakur — Wed, 06 Feb 2019 11:24:57 GMT

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

Re: Load data which is less then today()

bushpalaamarnat — Wed, 06 Feb 2019 11:25:05 GMT

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

Re: Load data which is less then today()

bushpalaamarnat — Wed, 06 Feb 2019 11:26:20 GMT

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

Re: Load data which is less then today()

pradosh_thakur — Wed, 06 Feb 2019 11:38:00 GMT

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

Re: Load data which is less then today()

bushpalaamarnat — Wed, 06 Feb 2019 11:42:36 GMT

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

Re: Load data which is less then today()

pradosh_thakur — Wed, 06 Feb 2019 11:50:27 GMT

Check in the variable overview what value do you see for vDateStamp2.

Re: Load data which is less then today()

bushpalaamarnat — Thu, 07 Feb 2019 11:20:02 GMT

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), '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 *, WeekStart(date(WeekStart_Date,'DD-MM-YYYY')) as WeekStartdate Resident Weekload; ICR: NoConcatenate load * Resident ICRLoad Where ("WeekStartsate" <= $(vDateStamp) ) //and ("WeekStartdate" >= $(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;

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), '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
*,
WeekStart(date(WeekStart_Date,'DD-MM-YYYY')) as WeekStartdate

Resident Weekload;

ICR:
NoConcatenate
load
*
Resident ICRLoad
Where ("WeekStartsate" <= $(vDateStamp) )
//and ("WeekStartdate" >= $(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;