Pass QVD name in a Variable and extract Max Date?

[dmohanty]

Hi All,

I have some Fact QVDs (For example ABC.qvd , XYZ.qvd etc etc).

I am running a script like this below to fetch the Max of TimeStamp into a variable so that, the variable can be used for next extraction of database.

set vDate = 0;

Max_Date:

Load

  max(ROW_UPDATE_STP) as max_date

FROM

[..\..\QVDs\TPTFacts\PURCH_ORDER_ITEM_FACT_Delta.qvd](qvd);

/********************************************

* Drop Table

********************************************/

//Drop Table temp;

/********************************************

* Set vLastExecTime

********************************************/

LET vDate = date(Peek('max_date',0,'Max_Date'),'M/D/YYYY hh:mm:ss.000000');

But I am doing this scrip for all Fact tables - by changing the Fact Table name at 'FROM' statement.

I needed help if this can be replaced by a variable and that variable will hold the Table Name. - This purpose is to make the scrip consistent and not to change every time for every table.

Any help here please?

[buzzy996]

try some thing like tis,

SET QVDFile = '$(vDrive)\CPA Documents\Batch Documents';

maxdateTab:

  // Get the max date from this QVD for use in incremental SELECT

  LOAD max(MODIFIEDTS) as maxdate

  FROM $(vQvdFile) (qvd);

  Let vmaxdate = timestamp(peek('maxdate'),'YYYY-MM-DD hh:mm:ss.ffffff');

  DROP table maxdateTab;

[anbu1984]

FOR Each TableNm in ABC.qvd BCD.qvd

set vDate = 0;

Max_Date:

Load

  max(ROW_UPDATE_STP) as max_date

FROM

[..\..\QVDs\TPTFacts\$(TableNm)](qvd);

...

Next

[dmohanty]

Hi Anbu,

Thanks for the help.

In the directory I have multiple files - seems it is not easy to write every name there in the FOR statement. Can'we give the folder name?

[maxgro]

FOR Each file in FileList('.\*.qvd')

  trace $(file);

Next;