Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenate Date and Time, find the MAX

Hello,

Cattura.JPG

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:

Cattura.JPG

Then I try to extract for each Practice the Max(Concat), with this:

Cattura1.JPG

But the result is a Table that have the field CONCAT empty. Why?

Thanks for the help.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

Get the Dates Right

View solution in original post

5 Replies
swuehl
MVP
MVP

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

Get the Dates Right

Not applicable
Author

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

sasiparupudi1
Master III
Master III

Try MaxString(CONCAT)

swuehl
MVP
MVP

Maybe try

Time(Time#(Num(TIME,'00000000'),'hhmmssff'))

Not applicable
Author

It works!
Thank you very much for the help!