Monthly QVD stores not working

[#1]

Hi

I am trying to store my transactional data to be split up into monthly QVDs in an output folder. I have written a script which gets all of the distinct months and years from the main transactional table and then I am looping through each of those months/years.

The actual transactional data is loading multiple QVDs from a folder which is already split by month/year. The purpose of this exercise is to load the extracts and apply any transformations on the split QVDs. This is for QVD load optimisation. .

Find below the script code:

SALETEST:
LOAD CODE,
     SALEDATE,
     SALETIME,
     ORDNO,
     LINENO,
     IVNO,
     IVDT,
     CUSTNO,
     CONTACTX,
     PNO,
     ORDQTY,
     OSPAMT,
     NETSALES,
     CPAMT,
     GMCPAMT,
     SURPNO,
     SURSPAMT,
     SURCPAMT,
     CREAID,
     MANPRICEID,
     TERMSTE,
     TERMSCE,
     SAUSERID,
     ORIGDT,
     OORDNO,
     OLINENO,
     OIVNO,
     CORDQTY,
     CUSTORDNO,
     BRNO,
     REASON,
     ORDTYPE,
     SALESMAN001,
     VATTOTAL,
     CUSTPNO,
     OBRNO,
     RBRCODE,
     PRODANAL,
     NEWLINE,
     CRQTY,
     NETORDQTY,
     NETSPAMT,
     NETCPAMT,
     NETGMCPAMT,
     NETOSPAMT,
     NETSURSPAMT,
     NETSURCPAMT,
     NETVATTOTAL,
     VATCODE,
     BASEPRICECODE,
     SRCESALE,
     PATROLID
FROM
[TEst Folder\*.qvd]
(qvd);


Dates:
LOAD DISTINCT
MONTH(DATE(SALEDATE)) AS MONTH, YEAR(DATE(SALEDATE)) AS YEAR




RESIDENT SALETEST;


LET vNoOfMthsYrs = NoOfRows('Dates');


FOR i = 0 TO $(vNoOfMthsYrs) - 1


LET vMth = PEEK('MONTH', $(i), 'Dates');
LET vYr = PEEK('YEAR', $(i), 'Dates');
MONTHLYSPLIT:
LOAD


* RESIDENT SALETEST WHERE MONTH(SALEDATE) = '$(vMth)' AND YEAR(SALEDATE) = '$(vYr)';

STORE MONTHSPLIT INTO SALES_$(vMth)_$(vYr).qvd (qvd);






NEXT


DROP TABLES SALETEST, Dates;

I am expecting to see a QVD for each Month/Year. When I check the destination folder, the QVDs are generated but when I check the actual data in the QVD, the dates stored in the QVDs are incorrect to what is on the QVD.

So for example in the QVD for Jun2014, I am getting dates for May 2014. This is the case for when I check another month, let's say July2014.

What have I done wrong?

Thanks

[kaushiknsolanki · Accepted solution]

HI,

     Add the drop table MONTHLYSPLIT after store table and check.

Regards,

Kaushik Solanki

[#3]

Hi. This has not worked. When you add the drop after the store, the table you store the next table no longer exists. Any other suggestions?

[marcus_malinow]

For the MonthlySplit load use a NOCONCATENATE, as it'll currently just be concatenating the results on to the existing SALETEST table

[#5]

Hello

I have got this to work.

When I was loading the table in the loop, I had to add NOCONCATENATE as before due to concatenation, it was unable to differentiate between the two tables.

Thanks for your help.