topic Create Monthly QVD's in QlikView

Create Monthly QVD's

rustyfishbones — Tue, 08 Oct 2013 15:40:49 GMT

Hi All,

I have a qvd file that contains 3 years of data.

I want to store the 3 years data into 36 monthly files

currently my file is called TrxHist.qvd

I want to store them as

TrxHist201001.qvd

TrxHist201002.qvd

TrxHist201003.qvd

TrxHist201004.qvd

TrxHist201005.qvd

TrxHist201006.qvd

TrxHist201007.qvd

TrxHist201008.qvd

And so on!

Re: Create Monthly QVD's

jvitantonio — Tue, 08 Oct 2013 15:53:20 GMT

Hello, you can use this script:

FOR year = 2010 to 2013

FOR month = 1 to 12

MyTable:

LOAD *

FROM

yourQVDFile (qvd)

where YEAR = $(year) and MONTH(MONTH) = $(month);

STORE MyTable INTO yourfolder\TrxHist$(year)$(month).qvd;

NEXT

YEAR is a field in your QVD called YEAR. If you don't have this field, use Year(yourDateField). The same for month.

I hope this helps.

Re: Create Monthly QVD's

bbi_mba_76 — Tue, 08 Oct 2013 15:55:05 GMT

Hi,

you could create a table TB1 with the 36 months, YYYYMM.

then you could use

LET v_rowTemp = NoOfRows('TB1'); // get the total number of rows in table

for i=1 to $(v_rowTemp)

LET v_month = peek('YYYYMM',$(i)-1,'TB1');

Re: Create Monthly QVD's

Tue, 08 Oct 2013 15:57:59 GMT

for i=1 to 36

let vMinDate= Num(Monthstart(addmonths(today(),-37 + $(i))));

temp1:

load *

where date<= $(vmindate)

store temp1 into TrxHist$(vmindate).qvd (qvd);

drop temp1;

next i;

Try this.. and let me know

Re: Create Monthly QVD's

Clever_Anjos — Tue, 08 Oct 2013 16:33:20 GMT

Q L, just a little tip, you must drop MyTable into that loop

Re: Create Monthly QVD's

jvitantonio — Tue, 08 Oct 2013 16:39:39 GMT

Clever, that's a clever answer This is just part of the code. By no means this is meant to be the finished scripts. I was trying to provide a quick response. Variables should use a better naming convension like vYear and also I would not set the years as hardcoded but use functions to make them flexible.

Thanks for the tip though

Re: Create Monthly QVD's

rustyfishbones — Thu, 10 Oct 2013 11:12:53 GMT

Thanks for that, it does work.

One final question, don't have data for 2013-11 and 2013-12

but I still get an outputted file for both.

How do I change the code to only out put the monthly qvd's if those months exist in the main qvd?

thanks

Re: Create Monthly QVD's

marcus_malinow — Thu, 10 Oct 2013 11:28:35 GMT

Alan,

you could try something like the code below. In it I first build up a list of year/month combinations that are present in the data, then output only the relevant files. In addition I have code in here to allow you to concatenate to existing qvd's, in case you want to do an incremental load.

YearMonthsFound:
NOCONCATENATE
LOAD concat(distinct YearMonthFound, ',') as YearMonthsFound;
LOAD Year & '-' & Month as YearMonthFound
RESIDENT WhateverTable;

LET vYearMonthsFound = peek('YearMonthsFound', -1);

DROP TABLE YearMonthsFound;

FOR EACH vYearMonthFound IN $(vYearMonthsFound)
TRACE $(vYearMonthFound);

WhateverTable_$(vYearMonthFound):

NOCONCATENATE LOAD *

RESIDENT WhateverTable

WHERE Year = num(left($(vYearMonthFound), 4))

AND Month = num(right($(vYearMonthFound), 2))

      IF vFullReload <> 1 THEN
          IF NOT ISNULL(QvdCreateTime('qvd\WhateverTable_$(vYearMonthFound).qvd')) THEN
               CONCATENATE (WhateverTable_$(vYearMonthFound))
               LOAD * FROM 'qvd\WhateverTable_$(vYearMonthFound).qvd' (qvd)
               WHERE NOT EXISTS (KeyField);
          ENDIF
      ENDIF

STORE WhateverTable_$(vYearMonthFound) INTO 'qvd\WhateverTable_$(vYearMonthFound).qvd';

DROP TABLE WhateverTable_$(vYearMonthFound);
NEXT vYearMonthFound

Re: Create Monthly QVD's

rustyfishbones — Thu, 10 Oct 2013 11:41:54 GMT

Hi Marcus,

I have tried your suggestion but it's not working.

I replaced WhateverTable with Transactions, which is the name of my main table

Am I missing something else?

Re: Create Monthly QVD's

marcus_malinow — Thu, 10 Oct 2013 11:45:58 GMT

Alan,

You may need to set up the variables in the script.

What does it say in the log file generated by your load? If one is not being generated, go to Settings, Document Properties, and select 'Generate Logfile' on the General tab.

Marcus

Re: Create Monthly QVD's

rustyfishbones — Thu, 10 Oct 2013 12:08:06 GMT

The log file wont run for some reason I get this error message

IF vFullReload <> 1

I 'm assuming I need to set a variable called vFullReload?

Re: Create Monthly QVD's

marcus_malinow — Thu, 10 Oct 2013 12:15:34 GMT

Hi Alan,

first issue - the load statement after the TRACE needs to change.

WhateverTable_$(vYearMonthFound):

NOCONCATENATE LOAD *

RESIDENT WhateverTable

WHERE Year = num(left($(vYearMonthFound), 4))

AND Month = num(right($(vYearMonthFound), 2))

second issue - vYearMonthsFound doesn't look to be being populated. Can you look at it in your variables and see if it contains a value?

Re: Create Monthly QVD's

rustyfishbones — Thu, 10 Oct 2013 12:20:59 GMT

Hi Marcus,

I will have a look now thanks for your help

Regards

Alan

Re: Create Monthly QVD's

rustyfishbones — Thu, 10 Oct 2013 12:35:56 GMT

Hi Marcus,

Here is what I have now and its not working

Re: Create Monthly QVD's

marcus_malinow — Thu, 10 Oct 2013 12:54:55 GMT

Alan,

I assume you're getting the same error as before? That was indicating that the vYearMonthFound variable was not being populated, which also probably means that vYearMonthsFound is also not being populated. Can you check in your variables that these have been created, and if so could you let me know what values they contain?

If vYearMonths is not being populated, this indicates an issue with the initial load into the YearMonthsFound table. This is probably the line LOAD Year & '-' & Month as YearMonthFound. Do you have fields named Year and Month in your source data? If not you need to substitute in appropriate fields, or apply Year(FieldName) & '-' & Month(FieldName). You'll also need to make a similar change further down the script when doing the RESIDENT load from Transactions into Transactions_$(vYearMonthFound).

Also, do you need the capability to merge this data in with data in existing YearMonth QVD's? If not, you can simply remove the IF vFullReload block.

Hope this helps.

Marcus

Re: Create Monthly QVD's

rustyfishbones — Thu, 10 Oct 2013 13:10:12 GMT

Hi Marcus,

Yes I have Fields called Year and Month in my Data

I have removed the block for the full reload.

I will keep trying with it

Re: Create Monthly QVD's

marcus_malinow — Thu, 10 Oct 2013 13:21:05 GMT

Hi Alan,

persist and I'm sure you'll be rewarded!

I would be looking at the two variables vYearMonthFound and vYearMonthsFound.

Perhaps also try putting an exit script after the initial load into YearMonthsFound and examine the data in there.

Marcus