topic Re: load data into qvds for each week in QlikView

load data into qvds for each week

Anonymous — Sat, 25 Jul 2015 14:51:29 GMT

Hi All,

I have two years data .i have to store the data into weekly qvd's. let's assume i have if i have two months data and i need to break the data into weekly qvds for every week.And qvd name should be the week start name.

Any help will be appreciated.

Re: load data into qvds for each week

sunny_talwar — Sat, 25 Jul 2015 15:08:17 GMT

Save your qvds like this may be:

FileName_201501.qvd

FileName_201502.qvd

and so on...

and when you try to pull them you can do this:

TableName

LOAD *

FROM FileName_*.qvd (qvd);

and this will auto-concatenate everything to the Table TableName

Hope this is what you were looking for? If not please explain...

Best,

Sunny

Re: load data into qvds for each week

maxgro — Sat, 25 Jul 2015 15:37:13 GMT

// test data, one year of dates stored in a qvd

load

date(makedate(2015) + rowno() -1) as date,

floor(rand()*100) as val

AutoGenerate 365;

STORE s into s.qvd;

// loop to store by week

t: load

date(min(date)) as mindate,

date(max(date)) as maxdate

from s.qvd (qvd);

let vmindate=peek('mindate');

let vmaxdate=peek('maxdate');

let vweekstart=(WeekStart(vmindate));

let vweekend=(WeekStart(vmaxdate));

do while vweekstart <= vweekend

trace vweekstart=$(vweekstart);

NoConcatenate load * from s.qvd (qvd)

where date >= '$(vweekstart)' and date <= date('$(vweekstart)'+6);

let file = 'w_' & date(Date#('$(vweekstart)'), 'YYYYMMDD');

trace file=$(file);

STORE w into '$(file)'.qvd (qvd);

DROP Table w;

let vweekstart=date(vweekstart+7);

loop;

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 05:24:31 GMT

Hi Sunny ,

Thanks for immediate reply !!

i need to load the data and i have to store the data into qvd's .

suppose if i have one year data with date .

lets start date =2014-01-01 and End Date = 2015-01-01

I need to run my database query on above condition.

suppose if 1st jan 2014 has 4 weeks then i have to seperate data for each week and store it in qvd .

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 08:07:02 GMT

thanks massimo!!

Hope your solution might work for me .

thank you

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 12:19:52 GMT

Hi massimo

i couldn't enter the loop.My script stops executing before the loop.

Re: load data into qvds for each week

sunny_talwar — Sun, 26 Jul 2015 12:24:18 GMT

What error did it give you before it entered the loop???

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 12:33:20 GMT

It's not the error , my script execution ends there .Any how i rectified.

but i need to name the qvd with its weekstartname for every week .

Re: load data into qvds for each week

sunny_talwar — Sun, 26 Jul 2015 12:41:54 GMT

This seems to be already doing that:

let file = 'w_' & date(Date#('$(vweekstart)'), 'YYYYMMDD');

trace file=$(file);

STORE w into '$(file)'.qvd (qvd);

DROP Table w;

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 12:45:09 GMT

lets asssume i have one month data .One month has 4 weeks .

while i am loading one months data first week's data should be stored in one qvd and this qvd should be named with weekstart of that particular week.

The same should be repeated for remaining 3 weeks and 3 qvds should be generated ,

thanks

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 12:51:11 GMT

Yeah!!

but i couln't populate data into qvds ,it might be my path problem

Re: load data into qvds for each week

Anonymous — Sun, 26 Jul 2015 13:30:52 GMT

The above code is giving weekstart of 1st year 1st month .it is giving the same weekstart for all the months.

But i need weekstart for every week .

Thanks

Re: load data into qvds for each week

maxgro — Sun, 26 Jul 2015 15:41:01 GMT

The script I already posted is in the attachment

I tried, reload and I get the s.qvd (test data) splitted in weeks qvd

example w_20150126.qvd has dates from 26 jan to 01 feb

Re: load data into qvds for each week

Anonymous — Mon, 27 Jul 2015 09:54:56 GMT

Thanks for your solution. Its working fine .It is breaking into week qvds.But the problem is when i am storing it into qvds is throwing error table not found.I have checked my path its correct .

weekstart:

load * Resident Data

Where SALES_DATE >='21/01/2015' and SALES_DATE <='22/06/2015';

let file = date(Date#('$(vweekstart)'),'YYYY/MM/DD');

TRACE file =$(file);

STORE weekstart into [$(mypath)/$(file)].qvd(qvd);

DROP Table weekstart;

Thanks

Re: load data into qvds for each week

maxgro — Mon, 27 Jul 2015 09:58:34 GMT

maybe table not found is weekstart

add a noconcatenate

weekstart:

noconcatenate load * Resident Data

Where SALES_DATE >='21/01/2015' and SALES_DATE <='22/06/2015';

let file = date(Date#('$(vweekstart)'),'YYYY/MM/DD');

TRACE file =$(file);

STORE weekstart into [$(mypath)/$(file)].qvd(qvd);

DROP Table weekstart;

Re: load data into qvds for each week

Anonymous — Mon, 27 Jul 2015 10:05:14 GMT

I used noconcatenate but it is not giving values week by week and moreover it script execution is failing .

Re: load data into qvds for each week

maxgro — Mon, 27 Jul 2015 10:15:44 GMT

could you post your script and your source data (qvd)?

Re: load data into qvds for each week

Anonymous — Mon, 27 Jul 2015 10:17:16 GMT

This is the error i am facing .

Re: load data into qvds for each week

Anonymous — Mon, 27 Jul 2015 10:32:18 GMT

Hi ,

My qvd is too large to attach.

But my script is below

Data:

LOAD

SALES_DATE,

From

[./QVD/SALES.QVD](qvd);

let vweekstart =WeekStart('21/01/2015');

let vweekend =WeekStart('22/06/2015');

do while vweekstart <= vweekend

trace vweekstart=$(vweekstart);

week:

load * Resident Data

Where SALES_DATE >='21/01/2015' and SALES_DATE <='22/06/2015';

let file = date(Date#('$(vweekstart)'),'YYYY/MM/DD');

TRACE file =$(file);

STORE week into [.\QVD\$(file).qvd];

DROP Table weekstart;

let vweekstart=date(vweekstart+7);

LOOP;

Re: load data into qvds for each week

Digvijay_Singh — Mon, 27 Jul 2015 11:12:09 GMT

DROP TABLE Weekstart will also fail, it should be 'week' instead.