topic FIRSTSORTEDVALUE not working with date in QlikView

FIRSTSORTEDVALUE not working with date

matthewgraci — Sat, 16 Nov 2024 00:44:55 GMT

I have a test table with two columns: Servers and ServersDate. Loading date from CSV using:

Date(Date#([ServersDate],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY') as ServersDate1

Servers,ServersDate1

23,04/01/2020

23,04/02/2020

24,04/03/2020

24,04/04/2020

I am trying to find the Server count for the oldest date:

=round(firstsortedvalue([DISTINCT] servers, ServersDate1)

This returns a null.

I have confirmed that ServersDate1 is a valid date by going into Number tab and changing it to an integer and it displayes incrementing numbers.

Cannot figuring out what I am doing wrong.

Thanks.

Re: FIRSTSORTEDVALUE not working with date

HirisH_V7 — Fri, 01 May 2020 15:57:21 GMT

Your Date transformation field should be this, remove hh mm.

Date(Date#([ServersDate],'MM/DD/YYYY'),'MM/DD/YYYY') as ServersDate1

Re: FIRSTSORTEDVALUE not working with date

matthewgraci — Fri, 01 May 2020 16:30:47 GMT

When I remove 'hh:mm' the date doesnt display at all.

Re: FIRSTSORTEDVALUE not working with date

HirisH_V7 — Fri, 01 May 2020 16:46:27 GMT

But in date mentioned in u r query doesn't have time in it. so your date# snippet should not have that.

PFA for ref

Re: FIRSTSORTEDVALUE not working with date

matthewgraci — Fri, 01 May 2020 16:47:10 GMT

I am sorry for the confusion. The date in the CSV file is: "MM/DD/YYYY hh:mm". I am converting it to date because if I dont it comes in as a string.

Thanks

Re: FIRSTSORTEDVALUE not working with date

HirisH_V7 — Sat, 02 May 2020 03:08:40 GMT

Ok then you need servers based on oldest time stamp or date. Based on that we can convert this...

To Extract only Date&Time: Timestamp(Timestamp#([ServersDate],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as ServersDateTime To Extract only Date: Date(Timestamp#([ServersDate],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY') as ServersDate

Re: FIRSTSORTEDVALUE not working with date

matthewgraci — Sat, 02 May 2020 12:40:46 GMT

I changed it to timestamp in the load. FirstSortedValue still is null.

Is there anything else I could be missing? Seems like a straightforward dataset for this function?

Thanks.