Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I state that the field Date and Time are in the format YYYYMMDD and hhmmssfff
I concatenated the field Date and Time with Data&''&Time:
Then I try to extract for each Practice the Max(Concat), with this:
But the result is a Table that have the field CONCAT empty. Why?
Thanks for the help.
Your CONCAT field values are interpreted as text. Max() function operates on numeric values.
Note that your TIME values seem to miss leading zeros and it looks like the fractions of second use only two digits 'ff'.
Maybe try to interpret your TIME values using Time#() function, same for DATA using Date# (), then just combine Date and time using numeric operation:
LOAD *,
Timestamp(DateInterpreted + TimeInterpreted) as TimestampField;
LOAD
DATA,
TIME,
Date(Date#(DATA,'YYYYMMDD')) as DateInterpreted,
Time(Time#(TIME,'hmmssff')) as TimeInterpreted
FROM ...;
Check that the new fields show correct values.
Now you should be able to find Max() of TimestampField
Your CONCAT field values are interpreted as text. Max() function operates on numeric values.
Note that your TIME values seem to miss leading zeros and it looks like the fractions of second use only two digits 'ff'.
Maybe try to interpret your TIME values using Time#() function, same for DATA using Date# (), then just combine Date and time using numeric operation:
LOAD *,
Timestamp(DateInterpreted + TimeInterpreted) as TimestampField;
LOAD
DATA,
TIME,
Date(Date#(DATA,'YYYYMMDD')) as DateInterpreted,
Time(Time#(TIME,'hmmssff')) as TimeInterpreted
FROM ...;
Check that the new fields show correct values.
Now you should be able to find Max() of TimestampField
Hello Stefan,
I tried the method and it works perfectly. But I have a problem.
The TIME field is in the format 'HMMSSFF' (8580060 for example) and in the format 'HHMMSSFF' (10582214 for example).
If I use the function Time(Time#(TIME,'hmmssff')) the fields that have the format 'HMMSSFF' are formatted correctly, but the fields that have the format 'HHMMSSFF' have a wrong Time (for example 10582214 becomes 01:06:22). And vice versa.
How can I fix it? Thanks for the help
Try MaxString(CONCAT)
Maybe try
Time(Time#(Num(TIME,'00000000'),'hhmmssff'))
It works!
Thank you very much for the help!