topic Re: List dates in order in QlikView

List dates in order

stjernvd — Fri, 18 Sep 2015 09:18:45 GMT

When I create this table, my dates are not in order...

Dates:

LOAD
Concat(Chr(39) & Date(Floor(date))& Chr(39), ', ' ) As date;
SQL SELECT
date
FROM xxx;

They come out like this '01/09/2015', '02/09/2015', 11/08/2015', and so on...

They dates should be in correct order, how do I fix that?

Re: List dates in order

MK_QSL — Fri, 18 Sep 2015 09:22:05 GMT

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ,dt_bus) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

Re: List dates in order

PrashantSangle — Fri, 18 Sep 2015 09:26:03 GMT

Hi,

Use Order By

try like

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST

order By dt_bus desc;

Re: List dates in order

sunilkumarqv — Fri, 18 Sep 2015 09:30:46 GMT

Dates:

LOAD
Concat(Chr(39) & dual(Date(Floor(dt_bus))& Chr(39), ', ' ),dt_bus) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST order by dt_bus;

Re: List dates in order

stjernvd — Fri, 18 Sep 2015 09:59:49 GMT

Hi Manish,

THank you very much this worked!

Just one last question, how do I get the date to be in the format 'yyyy-mm-dd' since it is currently in dd-mm-yyyy?

Re: List dates in order

MK_QSL — Fri, 18 Sep 2015 10:01:00 GMT

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ,dt_bus) As dt_bus;

Load

Date(Date#(dt_bus,'DD-MM-YYYY'),'YYYY-MM-DD') as dt_bus;

SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

Re: List dates in order

stjernvd — Fri, 18 Sep 2015 10:09:34 GMT

Hi Manish,

I did this:

Dates:

LOAD
Concat(Chr(39) & Date(Date#(dt_bus,'DD-MM-YYYY'),'YYYY-MM-DD')&Chr(39), ', ' ,dt_bus) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

and it gives me this in my listbox: '', '', '', '', etc...

what did I do wrong?

Re: List dates in order

MK_QSL — Fri, 18 Sep 2015 10:10:13 GMT

Check my edited reply again..

Re: List dates in order

stjernvd — Fri, 18 Sep 2015 10:14:33 GMT

same thing happened:(

is it because dt_bus had a time stamp associated with it?

So the original format was 10/08/2015 00:00:00....

Does that change how I load the statement?

Re: List dates in order

PrashantSangle — Fri, 18 Sep 2015 10:21:03 GMT

Hi,

I dont think so you required date#()

try without date#().

date(dt_bus,'YYYY-MM-DD')

Regards

Re: List dates in order

MK_QSL — Fri, 18 Sep 2015 10:22:18 GMT

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ,dt_bus) As dt_bus;

Load

Date(Floor(TimeStamp#(dt_bus,'DD/MM/YYYY hh:mm:ss')),'YYYY-MM-DD') as dt_bus;

SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;