How to change data during LOAD

[#1]

Hi

I have two fields in my data as follows:

Date	Time
2013.08.25	23:00
2013.08.26	00:00

Is it possible to change the date and time to the following when time is 00:00 (changes in highlighted in orange)

Date	Time
2013.08.25	23:00
2013.08.25	24:00

Many thanks

Shaun

[phaneendra_kunc]

have you tried IF statement?

IF(time(TimeField,'HH:MM')='00:00', '24:00',TimeField) as TimeField

IF(time(TimeField,'HH:MM')='00:00', Date(DateField-1,'YYYY.MM.DD'),DateField) as DateField

[#3]

For the date field, is it possible to take from previous record date rather than minus 1? This is because some dates are Mondays, and I want it to be Friday (i.e. the last record date) rather than Saturday (if using -1).

Thanks for your help!

[phaneendra_kunc · Accepted solution]

Yes, use Previous command..

IF(time(TimeField,'HH:MM')='00:00', Date(Previous(DateField),'YYYY.MM.DD'),DateField) as DateField

[#5]

Thanks for your help!