Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following load statement and I am having some issues restricting the load by the Maximum EFFECTIVEDATE.
LET vMaxDate = Max(EFFECTIVEDATE);
LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DATEMODIFIED
WHERE EFFECTIVEDATE = '$(vMaxDate)';
SQL SELECT CALLPUTIND,
EFFECTIVEDATE,
PRICE,
FROM "IXOMEX_OWNER".ZZPRICE;
Any help will be really appreciated.
Regards,
Daniel
A much better approach would be use your SQL to solve this
SELECT CALLPUTIND,
EFFECTIVEDATE,
PRICE,
FROM "IXOMEX_OWNER".ZZPRICE
where EFFECTIVEDATE = (select max(EFFECTIVEDATE) from FROM "IXOMEX_OWNER".ZZPRICE)
What issues are you having? That syntax looks wrong to me.
To do that, you need load Dates Table..
TEMP:
LOAD Date(TIMEMODIFIED,'DD/MM/YYYY') AS DATEMODIFIED;
SQL SELECT Distinct EFFECTIVEDATE
FROM "IXOMEX_OWNER".ZZPRICE;
MAX_DATE:
Load Max(DATEMODIFIED) as MaxDate resident TEMP;
LET vMaxDate = peek('MaxDate', 0, 'MAX_DATE');
FACTS:
LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DATEMODIFIED
WHERE EFFECTIVEDATE = '$(vMaxDate)';
SQL SELECT CALLPUTIND,
EFFECTIVEDATE,
PRICE,
FROM "IXOMEX_OWNER".ZZPRICE;
Hi,
Try this sequence
Sample:
LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DATEMODIFIED
WHERE EFFECTIVEDATE = '$(vMaxDate)';
SQL SELECT CALLPUTIND,
EFFECTIVEDATE,
PRICE,
FROM "IXOMEX_OWNER".ZZPRICE;
Sample1:
Load
Max(EFFECTIVEDATE) AS MaxEffectiveDate
resident Sample;
LET vMaxDate = peek(MaxEffectiveDate);//It will take the value from the table loaded previously
or
LET vMaxDate = peek(MaxEffectiveDate,0,'sample1')// You can specify the name explicitly
Hi James,
Thanks for your reply.
The full load statement is (I had issues loading this before):
vMaxDate = Max(EFFECTIVEDATE);
ODBC CONNECT32 TO [ACL_Treasury_Prod;DBQ=GLUKDBP1] (XUserId is TEST, XPassword is TEST1);
//-------- Start Multiple Select Statements ------
LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DATEMODIFIED
WHERE EFFECTIVEDATE = '$(vMaxDate)';
SQL SELECT CALLPUTIND,
CANCELEDIND,
EFFECTIVEDATE,
PRICE,
PRICENO,
PRICETITLEID,
QUOTETYPE,
SOURCEID,
FROM "IXOMEX_OWNER".ZZPRICE;
//-------- End Multiple Select Statements ------
The code returns no data. Therefore I believe there is an issue with the setting of the variable. The code works fine when the date is input as WHERE floor(EFFECTIVEDATE) > '10/11/2013'
Regards,
Daniel
Hi,
You can assign the maximum effective date to the variable before you load the field so there is no value in your variable then only no data is return. You have to divide your load statement like this
Sample:
LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DATEMODIFIED;
SQL SELECT CALLPUTIND,
CANCELEDIND,
EFFECTIVEDATE,
PRICE,
PRICENO,
PRICETITLEID,
QUOTETYPE,
SOURCEID,
FROM "IXOMEX_OWNER".ZZPRICE;
Sample1:
Load
Max(EFFECTIVEDATE) AS MaxEffectiveDate
resident Sample;
LET vMaxDate = peek(MaxEffectiveDate);
Test:
Load * resident sample
WHERE EFFECTIVEDATE = '$(vMaxDate)';
Try this or pls post your sample application
A much better approach would be use your SQL to solve this
SELECT CALLPUTIND,
EFFECTIVEDATE,
PRICE,
FROM "IXOMEX_OWNER".ZZPRICE
where EFFECTIVEDATE = (select max(EFFECTIVEDATE) from FROM "IXOMEX_OWNER".ZZPRICE)
Thank you for all your feedback.
Clever Anjos - that code works perfectly for what I am trying to achieve.
Regards,
Daniel