Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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);
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);
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');