topic Re: storing qvds in QlikView

storing qvds

Anonymous — Tue, 28 Jul 2015 05:34:52 GMT

Hi all,

The below is my script .i have to generate qvds weekly .The logicc is fine but i have to store them to qvd by each week.

while i am storing into qvds it is throwing error table not found.Guide me if any thing wrong in the script .

Base:

LOAD*

FROM

;

let vstartdate = '20/01/2015';

let venddate ='20/04/2015';

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

let vweekend =WeekStart('20/04/2015');

do while vweekstart<=vweekend

trace vweekstart=$(vweekstart);

week:

NoConcatenate load

* Resident Base

where SALES_DATE >= '$(vweekstart)' and SALES_DATE <= date('$(vweekend)');

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

trace file=$(file);

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

DROP Table w;

let vweekstart=date(vweekstart+7);

loop;

Thanks

Re: storing qvds

sushil353 — Tue, 28 Jul 2015 05:54:12 GMT

change this:

STORE week into $(file).qvd (qvd);

DROP Table week;

HTH

Sushil

Re: storing qvds

avinashelite — Tue, 28 Jul 2015 05:58:21 GMT

the DROP statement is wrong

DROP table week;

Re: storing qvds

Anonymous — Tue, 28 Jul 2015 05:58:43 GMT

I have tried that it is fine.But the problem is with no concatenate statement.

Re: storing qvds

Anonymous — Tue, 28 Jul 2015 06:00:31 GMT

yeah ! thats my mistake while posting .But store command should execute right ?

Re: storing qvds

jagan — Tue, 28 Jul 2015 06:11:58 GMT

Try this script

Base:

LOAD*

FROM

;

let vstartdate = '20/01/2015';

let venddate ='20/04/2015';

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

let vweekend =WeekStart('20/04/2015');

do while vweekstart<=vweekend

trace vweekstart=$(vweekstart);

week:

NoConcatenate

load

* Resident Base

where SALES_DATE >= '$(vweekstart)' and SALES_DATE <= date('$(vweekend)');

let file = 'week' & date(Date#('$(vweekstart)'), 'YYYYMMDD') & '.qvd';

trace file=$(file);

STORE week into [$(file)];

DROP Table week;

let vweekstart=date(vweekstart+7);

loop;

Re: storing qvds

Anonymous — Tue, 28 Jul 2015 06:13:40 GMT

i tried the below function in text box but the result is very strange .

weekstart(20/01/2015)

o/p :12/25/1899 which is wrong.

Is there any thing went wrong in syntax.

Thanks

Re: storing qvds

jagan — Tue, 28 Jul 2015 06:15:44 GMT

Hi,

Try like this

weekstart(MakeDate(2015, 1, 20))

Regards,

Jagan.

Re: storing qvds

avinashelite — Tue, 28 Jul 2015 06:15:51 GMT

did you check for that particular week you have data or not?? if their is no data then it would cause probelm.

I dont think its because of the no concatenate ...please provide the sample data let s check

Re: storing qvds

Anonymous — Tue, 28 Jul 2015 06:16:43 GMT

Hi Jagan ,

Thanks for reply

Its not working .It is not giving weekstart and weekend values.so its not iterating in loop.

Thanks

Re: storing qvds

avinashelite — Tue, 28 Jul 2015 06:17:28 GMT

Directly you cannot create a variable , you keep make use of the makedate function

Re: storing qvds

jonathandienst — Tue, 28 Jul 2015 06:22:06 GMT

rgv rand wrote:

i tried the below function in text box but the result is very strange .

weekstart(20/01/2015)

o/p :12/25/1899 which is wrong.

Is there any thing went wrong in syntax.

You have not included quotes, so this first working out 20 divided by 1 divided by 2015 and then applying weekstart to the resulting number.

Is your environment's date format DD/MM/YYYY? If it is, then WeekStart('20/01/2015') should work.

Re: storing qvds

qlikviewwizard — Tue, 28 Jul 2015 06:22:33 GMT

Hi rgvavihs

Comment the below statement and run.

DROP Table w;

Hope this will help you.

Re: storing qvds

Anonymous — Tue, 28 Jul 2015 06:35:07 GMT

Hi all,

Thanks all for your valuable time . I have resolved problem. i went wrong in weekstart variable .

Re: storing qvds

Anonymous — Tue, 28 Jul 2015 06:36:35 GMT

I tried it before i got null value

Re: storing qvds

Kushal_Chawda — Tue, 28 Jul 2015 09:41:35 GMT

Try this script. It's working for me

Data:

LOAD Distinct

Year(Date) as YEAR

FROM

(qvd);

LET vYearCnt= FieldValueCount('YEAR');

for i=1 to $(vYearCnt)

let vYear = FieldValue('YEAR',$(i));

YearFilter:

LOAD *,

Year(Date) as Year,

month(Date) as month,

week(Date) as Week,

date(WeekStart(Date),'DD-MM-YYYY') as WeekStart

FROM

(qvd)

where year(Date)='$(vYear)';

let vWeekCount = FieldValueCount('Week');

for j=1 to $(vWeekCount)

LET vWeekStart = FieldValue('WeekStart',$(j));

LET vWeek = FieldValue('Week',$(j));

WeekFilter:

NoConcatenate

LOAD *

Resident YearFilter

where Week='$(vWeek)';

STORE WeekFilter into "D:\Development\Qvd Generator\Filename_$(vWeekStart).qvd";

DROP Table WeekFilter;

NEXT j;

DROP Table YearFilter;

NEXT i;

DROP TABLE Data;