Loading data where date = Today()+1 (tomorrow)

[marteeke]

Hi everyone,

I'm trying to load only the data where my date (Leverdatum) is tomorrow.

I have tried "Where Leverdatum = Today()+1"  and other options/ways i've found like "'$(Today()+1)'".

When i try to load this i get the following error:

De volgende fout is opgetreden:

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294962067, ErrorMsg: [PSQL][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid user-defined or scalar function.

De fout is hier opgetreden:

SQL SELECT *

FROM Ord

Where Leverdatum = Today(0)+1

This is my load script:

LOAD

OrderNummer,

    Debiteur,

    RouteNr,

    Naam as Debiteurnaam,

    InpaklijstGeprint,

    OrgOrd as Backorder_van,

    Referentie,

    StatPost,

    Leverdatum,

    WssFlag,

    NaamIngever as IngeverOrderKop;

SQL SELECT *

FROM Ord

Where Leverdatum = Today()+1;

When i try this with a static date it does work.

E.g. "Where Leverdatum = '43396';"

I have tried other things but as i am pretty new to this i'm not sure they even make sense so I haven't included them all to keep this from getting very long and confusing.

Hope someone can point me in the right direction.

Thanks!

[Nicole-Smith]

It's because you're putting it in your SQL code but you're writing Qlik code.  SQL doesn't know what Today() is.

LOAD
OrderNummer,
    Debiteur,
    RouteNr,
    Naam as Debiteurnaam,
    InpaklijstGeprint,
    OrgOrd as Backorder_van,
    Referentie,
    StatPost,
    Leverdatum,
    WssFlag,
    NaamIngever as IngeverOrderKop;

SQL SELECT *
FROM Ord
Where Leverdatum = CAST(GETDATE()+1 AS DATE);

[marteeke]

Hi Nicole,

Thanks, that's good to know.

However with "CAST(GETDATE()+1 AS DATE)" I still get the same error.

De volgende fout is opgetreden:

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294962067, ErrorMsg: [PSQL][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid user-defined or scalar function.

De fout is hier opgetreden:

SQL SELECT *

FROM Ord

Where Leverdatum = CAST(GETDATE()+1 AS DATE)

Edit:

After some more searching i've come up with this, which seems to work for now.

LET vTomorrow = Date#(Today()+1);

LOAD

OrderNummer,

    Debiteur,

    RouteNr,

    Naam as Debiteurnaam,

    InpaklijstGeprint,

    OrgOrd as Backorder_van,

    Referentie,

    StatPost,

    Leverdatum,

    WssFlag,

    NaamIngever as IngeverOrderKop;

SQL SELECT *

FROM Ord

Where Leverdatum = $(vTomorrow);