topic Re: Find Last Time Per Days in QlikView

Find Last Time Per Days

Anonymous — Wed, 19 Jul 2017 08:27:58 GMT

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

Re: Find Last Time Per Days

Anil_Babu_Samineni — Wed, 19 Jul 2017 08:43:22 GMT

Script / Front End ??

Re: Find Last Time Per Days

Anonymous — Wed, 19 Jul 2017 08:45:59 GMT

Output is important

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

Script/ Visual does not matter

Re: Find Last Time Per Days

PrashantSangle — Wed, 19 Jul 2017 08:48:54 GMT

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,

Re: Find Last Time Per Days

Anonymous — Wed, 19 Jul 2017 08:55:00 GMT

it's not correct

Re: Find Last Time Per Days

PrashantSangle — Wed, 19 Jul 2017 08:58:09 GMT

Why????

Re: Find Last Time Per Days

tresB — Wed, 19 Jul 2017 08:58:53 GMT

May be this?

Re: Find Last Time Per Days

Anil_Babu_Samineni — Wed, 19 Jul 2017 09:00:12 GMT

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

PrashantSangle — Wed, 19 Jul 2017 09:05:28 GMT

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

Re: Find Last Time Per Days

Kushal_Chawda — Wed, 19 Jul 2017 09:08:03 GMT

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)

Re: Find Last Time Per Days

Anonymous — Wed, 19 Jul 2017 09:24:05 GMT

Thanks