data load from monthStart and CurrentDate -1

[Anonymous]

Hi All,

I am loading data for months and breaking the data into each monthly qvd with following script.

let vStartDate=Date(MonthStart(Today()),'YYYYMMDD');   /*StartDate for loading data*/

let vEndDate=Date(Today()-1,'YYYYMMDD');  /* End date will is CurrentDate -1*/

let vMonthStart = MonthStart(vStartDate);    /*  Picks MonthStart date of loadDate*/

let vMonthEnd = MonthEnd(vEndDate);          /*  Picks Monthend of EndLoadDate*/

do while vStartDate<=vEndDate   /*   loop starts until Startdate  and Enddate Matches */

let vNewMonthEnd =MonthEnd(vStartDate);

Table:

SELECT

    * from

WHERE

    DATE  BETWEEN '$(vStartDate)' AND '$(vNewMonthEnd)';

let file = Monthname(vStartDate);

TRACE file = $(file);  

let vStartDate = Date(MonthEnd(vStartDate)+1);

LOOP;

If VstartDate is 02/01/2016

i need to pull data for last month full data to CurrentDate -1.

Another condition is if currentDate is less than 5th of any month ,then it should pull data for last month full data

to CurrentDate-1.

Thanks

[petter]

Something must be missing in your Load Script fragment... The purpose of the LOOP statement ... could you please explain. There is also something missing after the FROM in the SELECT.

[Anonymous]

Hi

Thanks for reply!,

I want load data between Date(MonthStart(Today())) and Date(Today()-)

If Date(Today()) <=5th day of particular month then i want to load data from monthstart(previousmonth) and Date(Today()-1)

.

Hope you understand my requirement

[HirisH_V7]

Hi,

Do you require front end selections or what like this,

Check out,

PFA

-hirish

[Anonymous]

Hi,

No it should be in the script .

[PrashantSangle]

Hi,

I am little bit curious about your variable defination.

let vStartDate=Date(MonthStart(Today()),'YYYYMMDD');   /*StartDate for loading data*/

let vEndDate=Date(Today()-1,'YYYYMMDD');  /* End date will is CurrentDate -1*/

In your above variable defination what if your today is monthstart

will ever your vstartDate and vEndDate satisfied this condition.

vStartDate<=vEndDate 

Regards,

[HirisH_V7]

Hi,

Calendar with period flags

There you can see 30 day period count . you can include that logic in your Calendar if your data running real time data means you can use Today()  or else go with your max(Date) field .

look into this,

Hope this helps ,

-Hirish

[Anonymous]

Hi Max,

Ignore the above the question My exact requirement is

I want to load data for one month.

StartDate should be MonthStart(Today) and EndDate is Today()-1.

but if date(Today()) is lessthan 5th of particular month ,then it should load data from Previous full month  data and Today()-1

Thanks

[PrashantSangle]

Hi,

try this variable defination

Let vTestDate=if(Day(Today())<=5,Date(Monthstart(AddMonths(Today(),-1)),'YYYY-MM-DD'),Date(MonthStart(Today()),'YYYY-MM-DD'));

Regards

[Anonymous · Accepted solution]

Hi Max

Thanks for the reply

i did similar to you .

if Date(Today())<=Date(MonthStart(Today())+4) then

let vStartDate = Date(MonthStart(MonthStart(Today())-1),'YYYYMMDD');

let vEndDate =Date(Today()-1,'YYYYMMDD');

select * from where 'vStartDate' between 'vEndDate'

table;

else

let vStartDate = Date(MonthStart(Today()),'YYYYMMDD');

    let vEndDate =Date(Today()-1,'YYYYMMDD');

select * form table where 'vStartDate' between 'vEndDate'

end if

my code is working. Thanks for your time .

thank you