Find Last Time Per Days

[Anonymous]

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.

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

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

[Anil_Babu_Samineni]

Script / Front End ??

[Anonymous]

Output is important

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

Script/ Visual does not matter

[PrashantSangle]

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,

[Anonymous]

it's not correct

[PrashantSangle]

Why????

[tresesco]

May be this?

[Anil_Babu_Samineni]

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

[PrashantSangle]

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

[Kushal_Chawda · Accepted solution]

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)