topic Re: load data between months in QlikView

data load from monthStart and CurrentDate -1

Anonymous — Wed, 30 Dec 2015 11:57:39 GMT

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

Re: load data between months

petter — Wed, 30 Dec 2015 15:25:27 GMT

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.

Re: load data between months

Anonymous — Wed, 30 Dec 2015 19:42:53 GMT

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

Re: load data between months

HirisH_V7 — Thu, 31 Dec 2015 04:40:20 GMT

Hi,

Do you require front end selections or what like this,

Check out,

PFA

-hirish

Re: load data between months

Anonymous — Thu, 31 Dec 2015 04:41:25 GMT

Hi,

No it should be in the script .

Re: load data between months

PrashantSangle — Thu, 31 Dec 2015 04:48:35 GMT

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,

Re: load data between months

HirisH_V7 — Thu, 31 Dec 2015 04:51:14 GMT

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

Re: load data between months

Anonymous — Thu, 31 Dec 2015 04:55:56 GMT

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

Re: load data between months

PrashantSangle — Thu, 31 Dec 2015 05:17:01 GMT

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

Re: load data between months

Anonymous — Thu, 31 Dec 2015 05:45:25 GMT

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