Incremental Load, Date Format, SQL Select

[hobanwashburne]

My overall goal is to read in the max date value from a previously created QVD then use that date in a SQL statement to pull in any data created after that date.

So far I am using the following script to get the Max Date:

temp:
LOAD ShipDate
FROM

(qvd);

MaxDate:
LOAD max(ShipDate) as MaxDate;

load FieldValue('ShipDate', IterNo()) as ShipDate
AutoGenerate(1)
While not IsNull(FieldValue('ShipDate', IterNo()));

Drop Table temp;

This return a table named MaxDate containing one field named MaxDate having the value 41670 (which translates to 1/31/2014)

The problem is that the database that I am pulling the new information from uses the date format YYYYMMDD. So somehow I need to translate 41670 into 20140131 and write it into a variable so that I can use it in the following:

//Load existing QVD

US:
LOAD SalesOrder,
     SalesOrderKey,
     ReqShipDate,
     ShipDate
FROM

(qvd);

//Add new Data
      Concatenate LOAD
      VLSLSO & '-' & VLSLSL as SalesOrder,
      VLCOMP & '-' & VLSLSO & '-' & VLSLSL as SalesOrderKey,
      Date#(VLRDAT,'YYYYMMDD') as ReqShipDate,
      Date#(VLSDAT, 'YYYYMMDD') as ShipDate;
SQL SELECT *
FROM networkpath.TableName

where VLSDAT > MaxDate;

[Nicole-Smith · Accepted solution]

temp:
LOAD ShipDate
FROM

(qvd);

MaxDate:
LOAD max(ShipDate) as MaxDate;

load FieldValue('ShipDate', IterNo()) as ShipDate
AutoGenerate(1)
While not IsNull(FieldValue('ShipDate', IterNo()));

Drop Table temp;

LET vMaxDate = date(PEEK('MaxDate', 0, 'MaxDate'), 'YYYYMMDD');

DROP TABLE MaxDate;

//Load existing QVD

US:
LOAD SalesOrder,
     SalesOrderKey,
     ReqShipDate,
     ShipDate
FROM

(qvd);

//Add new Data
      Concatenate LOAD
      VLSLSO & '-' & VLSLSL as SalesOrder,
      VLCOMP & '-' & VLSLSO & '-' & VLSLSL as SalesOrderKey,
      Date#(VLRDAT,'YYYYMMDD') as ReqShipDate,
      Date#(VLSDAT, 'YYYYMMDD') as ShipDate;
SQL SELECT *
FROM networkpath.TableName

where VLSDAT > $(vMaxDate);

[linusblomberg]

I had this same problem once. I solved it by converting the format of my max date variable in the Select statement.

Where FULL_DATE >= to_date('$(LastReloadDate)','MM/DD/YYYY');