Last hour of every day value related

[FranciscoQ]

Hi !

I need some help with this:

I have a a Currency Exchange database, that show values for each hour of each day, the thing is that I need the value related to the last hour of each day.

Ex:

Day                                                         CE

10/01/2020 ,     10:00                        2,32 

10/01/2020 ,     10:55                        2,33

10/01/2020 ,     11:04                        2,35

.......                         ........... 

And what I need is:

Day                            Time                        CE

10/01/2020            22:05                        2,36

11/01/2020            21:07                        2,41

.......                         ...........                        ..........

So I've tried with the next Script:

CE_temp:
LOAD

Day,
Timestamp(Day,'hh:mm') AS Time,
CE,

FROM [lib://DEV_STG_WEB/QVD/STG_WEB_CE.QVD]
(qvd);

Left Join(CE_temp)
Load
Day,
Max(Time) AS Max_Hour,
1 AS Flag_max_hour
Resident CE_temp

Group By Day;

So I can detect then all the values with flag_max_hour = 1, and I can create another table filtering those values

The problem is that I am noticing that It's putting flag_max_hour for every hour ! Not just the max hour,

So I have

Day                      Hour            CE            Flag_max_hour                                              

10/01/2020      10:00          2,32                       1

10/01/2020      10:55          2,33                       1

10/01/2020      11:04          2,35                       1

I would really appreciate some help!

[Vegar · Accepted solution]

Try adjusting your script to this.

CE_temp:
LOAD
Day,
Timestamp(Day,'hh:mm') AS Time,
CE
FROM [lib://DEV_STG_WEB/QVD/STG_WEB_CE.QVD]
(qvd);

Left Join(CE_temp)
Load
Day,
Max(Time) AS Time,
1 AS Flag_max_hour
Resident CE_temp

Group By Day;

If you want to skip the Flag and reduce directly in the join, then you can replace the Left Join with Right Join or with Inner Join to achieve this. Like this.

CE_temp:
LOAD
Day,
Timestamp(Day,'hh:mm') AS Time,
CE
FROM [lib://DEV_STG_WEB/QVD/STG_WEB_CE.QVD]
(qvd);

Inner Join(CE_temp)
Load
Day,
Max(Time) AS Time
//,1 AS Flag_max_hour
Resident CE_temp

Group By Day;

[FranciscoQ]

That worked perfect !

Thank you very much!

[Vegar]

Glad I could help.