topic How to change data during LOAD in QlikView

How to change data during LOAD

Sat, 25 Jan 2014 23:30:29 GMT

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

Re: How to change data during LOAD

phaneendra_kunc — Sat, 25 Jan 2014 23:43:42 GMT

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

Re: How to change data during LOAD

Sun, 26 Jan 2014 00:41:07 GMT

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!

Re: How to change data during LOAD

phaneendra_kunc — Sun, 26 Jan 2014 00:49:13 GMT

Yes, use Previous command..

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

Re: How to change data during LOAD

Sun, 26 Jan 2014 01:06:53 GMT

Thanks for your help!