Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

babakpirooz
New Contributor

Find Last Time Per Days

Hello

We have the same data as below In Qlikview

i want Find Last Time/Value In Days.

That means a record for each day.

TestData.png

---------------------------------------------------------------------

Result Look Like :

Date                                   Value

1/1/2013   10:56 PM              15
12/11/2013 11:00 PM              19
12/11/2014   6:00 AM               26

1 Solution

Accepted Solutions

Re: Find Last Time Per Days

try this

Data:

LOAD Date as DateTime,

     Date(floor(Date)) as Date,

     Value

FROM

[New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Date,

     date(max(DateTime)) as DateTime,

     1 as Flag

Resident Data

Group by Date;

On front end create straight table

Dimension:

DateTime

Expression:

=sum({<Flag={1}>}Value)

10 Replies

Re: Find Last Time Per Days

Script / Front End ??

babakpirooz
New Contributor

Re: Find Last Time Per Days

Output is important

---------------------------

Script/ Visual does not matter

Re: Find Last Time Per Days

chk firstSortedValue() or min()

Before that you have to create one more field which gives you only date from datetime field

Try below

Test:

Load Date as DateTime, Date(floor(Date)) as Date,Value from tableName;

Final:

Load Date,min(Value) as min_value Resident Test group by Date;

Regards,

Regards,
Prashant Sangle
babakpirooz
New Contributor

Re: Find Last Time Per Days

it's not correct

Re: Find Last Time Per Days

Why????

Regards,
Prashant Sangle
MVP
MVP

Re: Find Last Time Per Days

May be this?

Capture.PNG

Re: Find Last Time Per Days

You must explain the reason as well if not suit to you. And check tresco image where prashant recoomend.

Re: Find Last Time Per Days

Hi,

You can also try with firstsortedValue()

try below script

Test:

LOAD Date(floor(Date)) as Date,

Date as DateTime,

     Value

FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

Final:

Load Date,FirstSortedValue(Value,-DateTime) as min_value Resident Test group by Date order by Date;

Regards,

Prashant

Regards,
Prashant Sangle

Re: Find Last Time Per Days

try this

Data:

LOAD Date as DateTime,

     Date(floor(Date)) as Date,

     Value

FROM

[New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Date,

     date(max(DateTime)) as DateTime,

     1 as Flag

Resident Data

Group by Date;

On front end create straight table

Dimension:

DateTime

Expression:

=sum({<Flag={1}>}Value)