topic Re: Problem with multiple date selections in QlikView

Problem with multiple date selections

Tue, 27 Jan 2015 15:38:32 GMT

Hi All,

I have a .csv in which they unfortunately don't specify the date. It is imported as a text string which looks like: 20150127 (for 27th of January 2015). I have added the following string into the script to make sure it loads as a date:

LOAD Datum,

Date(MakeDate(Left(Datum,4),Mid(Datum,5,2),Right(Datum,2)),'YYYY-MM-DD') as Datum_New,

So far so good. I can also specify list boxes with separate Year, Month and Day functions which work. But as soon as I start making multiple selections; i.e. 2015 and March, it isn't having it. It will not select multiple selections from these boxes. How to fix this? Or a better overall solution to the problem of having these date issues in the first place?

Thank you very much!

/Maarten

Re: Problem with multiple date selections

MarcoWedel — Tue, 27 Jan 2015 16:15:24 GMT

Hi,

I suppose you are selecting in calculated listboxes rather than listboxes using fields directly.

So instead of creating listboxes with expressions like month(Datum) or year(Datum) try to generate separate fields for month and year in the script.

Otherwise selections in the month(Datum) listbox reset selections in the year(Datum) listbox (because in fact they both apply selections to the same field).

One solution to do so could be:

LOAD *,

Month(Datum) as Month,

Year(Datum) as Year;

LOAD Date(Date#(Datum, 'YYYYMMDD'),'YYYY-MM-DD') AS Datum

Someotherfields

From yourdatasource;

Hope this helps

Regards

Marco

Re: Problem with multiple date selections

Tue, 27 Jan 2015 18:59:28 GMT

Perfect! Thank you very much. Easier solution as well

Re: Problem with multiple date selections

MarcoWedel — Tue, 27 Jan 2015 19:40:52 GMT

You're welcome.

Regards

Marco

Re: Problem with multiple date selections

Wed, 01 Apr 2015 10:53:13 GMT

EDIT: The Problem has been solved by now.

Hello,

I had pretty much the same problem. I also used calculated listboxes instead of listboxes using fields directly. So I tried as explained above, but I received an error. Here is what I did:

Generic

SQL SELECT

TIMESTAMP,

DATA,

WERT,

YEAR(TIMESTAMP) as Jahr

FROM Source01;

and I got this Error:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

Generic
SQL SELECT
    TIMESTAMP,
    DATA,
    WERT,
    YEAR(TIMESTAMPSOURCELT) as Jahr
FROM Source01;

Thinking the 'Generic' Keyword could cause the error, I tried it with a different Source:

SQL SELECT

NR as "Nummer",

TimeStamp,

Year(TimeStamp) as Jahr

FROM Source02;

I received the same error.

Obviously the 'Year' command does not work, but why? I'd be very grateful, if someone could help me.

Kind Regards,

phz

Re: Problem with multiple date selections

Miguel_Angel_Baeyens — Wed, 15 Apr 2015 10:50:08 GMT

Philipp,

Year() is a valid function for Oracle for date fields, but you may need to use another one like To_Date().

See Oracle documentation:

TO_DATE‌

Miguel