topic Loading the date in descing order in QlikView

Loading the date in descing order

kunkumnaveen — Thu, 08 Jul 2021 06:30:54 GMT

Hello All,

I was trying to load the dates in descending order ,hence I am doing the below enclosed steps at backend but for some reason its not working

Sample Data

t:
load
Date(Date,'DD/MM/YYYY') as Date,
value,
Date(Date,'MMYY') as MonthYear;
load * Inline
[
Date,value
10/05/2021,1000
11/05/2021,2000
29/05/2021,3000
05/06/2021,4000
07/06/2021,5000
30/06/2021,6000
];

load
Date,
value
resident t
order by Date desc ;
drop table t;

Current Output :

Required Output:

30/06/2021

07/06/2021

05/06/2021

29/05/2021

11/05/2021

10/05/2021

Thanks

Re: Loading the date in descing order

marcus_sommer — Thu, 08 Jul 2021 07:17:17 GMT

The sorted table doesn't change the order within the field-values by an existing field - means you need a new field for it, for example with:

load
Date as SortedDate,
value
resident t
order by Date desc ;
drop table t;

rename field SortedDate to Date;

Further you should change:

Date(Date,'MMYY') as MonthYear

into something like:

Date(floor(monthstart(Date)),'MMYY') as MonthYear

because a formatting like date() doesn't change the values - they just look differently.

- Marcus

Re: Loading the date in descing order

kunkumnaveen — Thu, 08 Jul 2021 09:47:05 GMT

Hello Marcus,

Thanks for the reply

I was using your code for test purpose on sample data , but still the output remains same ,I mean it doesn't change anything

Code

load
Date as SortedDate,
value
resident t
order by Date desc ;
drop table t;

Do I am missing anything in the code

thanks

Re: Loading the date in descing order

kunkumnaveen — Thu, 08 Jul 2021 10:41:17 GMT

Hello All, any suggestions on the above requirement please

Re: Loading the date in descing order

avinashelite — Thu, 08 Jul 2021 10:54:20 GMT

Have to tried to use sorting (DESC) from the list box properties ?

Re: Loading the date in descing order

kunkumnaveen — Thu, 08 Jul 2021 11:06:38 GMT

Hi ,I am showing it in any list box, if you see the above pic I have them on selection strip on top

Re: Loading the date in descing order

kunkumnaveen — Thu, 08 Jul 2021 11:15:27 GMT

Sorry ,ignore the earlier message

Hi ,I am Not showing it in any list box, if you see the above pic I have them on selection strip on top ,I mean on open the app default max date get select

Re: Loading the date in descing order

marcus_sommer — Fri, 09 Jul 2021 06:25:34 GMT

With my suggestion the sorting on the field-level will work. How the values are displayed within the UI is mainly independent from it because all the objects have an own sorting-feature and per their default-settings they don't use the load-order for the sorting else they try to interpret the content respectively are using the automatically created and/or intentionally changed/adjusted field-tags for it. This means the sorting-order must be adjusted if you want a different one.

I'm not sure but I think the selection-panel from Sense isn't a typically object which has the usually object-properties else it's a specialized object which may in parts be adjusted through the document-properties - at least in View you could set the sorting and formatting of the fields there on a document-global level. I could imagine that this is similar in Sense unless that those settings are in a different place. I'm not familiar enough with Sense to say where it might be - just take a look on settings on a document-level, it might be also by the master-items.

- Marcus