Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My first foray into incremental loads is not going as planned I have used the following steps and am now somewhat stuck.
1. load previous qvd file
2. Create new table with max date of file
3. Set variable to equal max data from file (using let and peek statement)
all of this works fine...i will drop the temp table once I can get this to work...my problem is getting the SQL Select (via ODBC connection) to recognise the variable within the where statement. I have tried a few different methods (preceding load, Setting the variable, letting the variable, adding quatations, removing quotations, but I cant get the syntax right. Any help would be greatly appreciated
As per your comments, the variable didn't have the date field,
Use the TRACE command to show the value of variable in the load script and also check the log file after fail, then you can see which value holding in the variable.
TRACE Max Date value ----> $(vMaxDate);
Can you please post your Variable definition in LET statement ?
Hi Dathu
I have tried a couple of variations for the LET statement
LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD'); returns 2007 for some reason
LET vMaxNPS = Date(Peek(MaxNPSDate,0,MaxNPSTable),'YYYY-MM-DD'); returns -
TRACE of the vMaxNPS shows blank
and when I debug, the LET statement appears to read the field MaxNPSDate from the MaxNPSTable as NULL
Can you please post whole script what are you using.
I am suspecting the problem with MaxNPSTable and how many values exist in this table?
This table must have only one value and that would be maximum date value. Please dont drop the table and check what is the value on the table ? In the this table having only one date value then there shouldn't be a problem.
Hi Dathu
The table only contains a single value
Here is the complete script
NPS:
LOAD
FILE_DATE,
FROM
nps.qvd
(qvd);
MaxNPSTable:
LOAD
Max(FILE_DATE) AS
FROM NPS.qvd(qvd);
LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');
ODBC CONNECT TO SDCC;
NPSNEW:
SQL SELECT
Date as 'FILE_DATE',
,
FROM dashboarddata.nps
where Date > $(vMaxNPS);
Rick
Try like below:
NPS:
LOAD
FILE_DATE,
FROM nps.qvd (qvd);
MaxNPSTable:
LOAD Max(FILE_DATE) AS MaxNPSDate
FROM NPS.qvd(qvd);
LET vMaxNPS = Date(Peek('MaxNPSDate'),'YYYY-MM-DD');
ODBC CONNECT TO SDCC;
NPSNEW:
SQL SELECT
Date as 'FILE_DATE',
,
FROM dashboarddata.nps
where Date > '$(vMaxNPS)';
if not work, what is value you are getting in the MaxNPSTable (date or string) ?
Hi Rick,
Please load two statement below only and give me the result:
MaxNPSTable:
LOAD Max(FILE_DATE) AS [MaxNPSDate] FROM NPS.qvd (qvd);
// What is the value of field [MaxNPSDate]?
LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');
// What is the value of variable vMaxNPS?
Awaiting your prompt response.
Regards,
Sokkorn
Hi Sokkorn
MaxNPSTable:
LOAD Max(FILE_DATE) AS FROM NPS.qvd (qvd);
Returns a value of 41645 (excel equivalent of 6th Jan 2014)
LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');
When specified as =vMaxNPS shows 2014-01-06 but when specified as =$(vMaxNPS) shows 2007
Rick
Hi Rick,
Here is my test
Result after load
You see the line that I use TRACE function with $(vMaxNPS), then it return 2014-01-06. Not sure why you got 2007
Regards,
Sokkorn
Hi Rick, can you check what is your SET DateFormat variable ?
Try Like below:
LET vMaxNPS = text(Date(Peek('MaxNPSDate'),'YYYY-MM-DD')) ;
ODBC CONNECT TO SDCC;
NPSNEW:
SQL SELECT
Date as 'FILE_DATE',
,
FROM dashboarddata.nps
where Date > '$(vMaxNPS)';