Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
rgvavihs
Valued Contributor

data load from monthStart and CurrentDate -1

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

1 Solution

Accepted Solutions
Highlighted
rgvavihs
Valued Contributor

Re: load data between months

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

View solution in original post

9 Replies
Highlighted
MVP
MVP

Re: load data between months

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.

Highlighted
rgvavihs
Valued Contributor

Re: load data between months

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

Highlighted
HirisH_V7
Honored Contributor

Re: load data between months

Hi,

Do you require front end selections or what like this,

Check out,

PFA

-hirish

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
rgvavihs
Valued Contributor

Re: load data between months

Hi,

No it should be in the script .

Highlighted

Re: load data between months

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
HirisH_V7
Honored Contributor

Re: load data between months

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

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
rgvavihs
Valued Contributor

Re: load data between months

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

Highlighted

Re: load data between months

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

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
rgvavihs
Valued Contributor

Re: load data between months

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

View solution in original post