topic Re: Load Dates in QlikView

Load Dates

danielnevitt — Mon, 11 Nov 2013 12:56:08 GMT

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

Re: Load Dates

ThornOfCrowns — Mon, 11 Nov 2013 13:03:13 GMT

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

Re: Load Dates

bruno_m_santos — Mon, 11 Nov 2013 13:07:01 GMT

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;

Re: Load Dates

Mon, 11 Nov 2013 13:10:29 GMT

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

LET vMaxDate = peek(MaxEffectiveDate,0,'sample1')// You can specify the name explicitly

Re: Load Dates

danielnevitt — Mon, 11 Nov 2013 13:11:39 GMT

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

Re: Load Dates

Mon, 11 Nov 2013 13:18:32 GMT

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

Re: Load Dates

Clever_Anjos — Wed, 13 Nov 2013 01:30:50 GMT

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)

Re: Load Dates

danielnevitt — Wed, 13 Nov 2013 12:34:45 GMT

Thank you for all your feedback.

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

Regards,

Daniel