How to load specific no of qvds

[#1]

Hi All,

I have qvds for each month for 5 years, file format is Table_yyyy_mm.qvd,  now I want to load qvds form a variable which is holding a value created from an excel file.

excel file will have field like "Number of months"  It will update daily. from here variable will take the number. according to this number application has to load only those months of qvds from latest to oldest.

LET vmindate= num(AddYears(num(Today()),-5));

Sales:
LOAD Rand()+24 as Sales,
     Date(Date,'MM/DD/YYYY') AS Date;
LOAD $(vmindate)+RecNo()-1 as Date
AUTOGENERATE num(Today())-$(vmindate);

LET vcurrentdate= num(MonthStart(AddYears(num(Today()),-3))); // Set start date

DO WHILE vcurrentdate <= Today()

   ExportSales:
   NOCONCATENATE
   LOAD *
   RESIDENT Sales
   WHERE InMonth(Date, $(vcurrentdate), 0) = true();

   LET vdatestring = Date($(vcurrentdate), 'YYYY_MMM');
   STORE ExportSales INTO [..\$(vdatestring).QVD] (qvd);
   DROP Table ExportSales;

   LET vcurrentdate = num(AddMonths($(vcurrentdate), 1));
  
LOOP

Ex:   if excel file has the value 15 in the field "Number of months " , then Application should load only latest 15 moths data from the qvds. how can I do this

[jagan]

Hi,

Read the Number of Months and store it in variable using Peek() like below

NoOfMonths:

LOAD

[Number of months] AS NumberOfMonths

From ExcelFile;

LET vNoOfMonths = Peek('NumberOfMonths');

LET vmindate= num(AddYears(num(Today()),-5));

Sales:

Sales:

LOAD Rand()+24 as Sales,

     Date(Date,'MM/DD/YYYY') AS Date;

LOAD MonthStart(Today(), - $(vNoOfMonths) + RecNo()-1) as Date

AUTOGENERATE ($(vNoOfMonths));

LET vcurrentdate= num(MonthStart(Today(), -vNoOfMonths); // Set start date

DO WHILE vcurrentdate <= Today()

   ExportSales:
   NOCONCATENATE
   LOAD *
   RESIDENT Sales
   WHERE InMonth(Date, $(vcurrentdate), 0) = true();

   LET vdatestring = Date($(vcurrentdate), 'YYYY_MMM');
   STORE ExportSales INTO [..\$(vdatestring).QVD] (qvd);
   DROP Table ExportSales;

   LET vcurrentdate = num(AddMonths($(vcurrentdate), 1));
  
LOOP

Hope this helps you.

Regards,

Jagan.

[#3]

Thanks Jagan mohan,

in the code that you provided is restricting the moths at the initial step but I don't want it I want to load those qvds in the second step.

Ex:    I have data for 5 years.

I want to generate monthly qvds for 5 years means 4*12=48+ current year 9 months= 57 moths qvds I need to have.

those qvds format is like  table_2015_Sep.qvd, table_2015_Aug.qvd

Best Regards

John

[jagan]

Change this section to read number of months from Excel file

NoOfMonths:

LOAD

[Number of months] AS NumberOfMonths

From ExcelFile;

LET vNoOfMonths = Peek('NumberOfMonths');