Create Monthly QVD's

[rustyfishbones]

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!

[jvitantonio · Accepted solution]

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

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.

J

[bbi_mba_76]

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');

     <load in a tmp table fromTrxHist.qvd with a filter based on v_month>

      <store using the variable for name>

     next

[#4]

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

[Clever_Anjos]

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

[jvitantonio]

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

[rustyfishbones]

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

[marcus_malinow]

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

[rustyfishbones]

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?

[marcus_malinow]

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