Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielnevitt
Creator
Creator

Load Dates

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

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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)


View solution in original post

7 Replies
ThornOfCrowns
Specialist II
Specialist II

What issues are you having? That syntax looks wrong to me.

bruno_m_santos
Partner - Creator
Partner - Creator


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;

Not applicable

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


danielnevitt
Creator
Creator
Author

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

Not applicable

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

Clever_Anjos
Employee
Employee

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)


danielnevitt
Creator
Creator
Author

Thank you for all your feedback.

Clever Anjos - that code works perfectly for what I am trying to achieve.

Regards,

Daniel