how to Assign value to the rows ?

[gauravgg]

I have a column in which i have Time as follows

Time

12.00 AM

12.14 AM

13.12 AM

03.30 PM

04.00 PM

02.30 AM

.

.

.

n numbers of rows

I want to assign  it as following

Time                        Assign Value

12.00 AM                    12.00 AM

12.14 AM                    12.00 AM

13.12 AM                     13.00 AM

03.30 PM                     03.30 PM

04.00 PM                     04.00 AM

02.30 AM                     02.30 AM

.

.

n numbers of rows

Thanx in Advance

[#2]

Hi Gaurav,

You can get by using substring and instr functions. But in qlikview we have mid and index functions. Based on this we will get the required output.

input:

load * inline [

Time

12.00 AM

12.14 AM

13.12 AM

03.30 PM

04.00 PM

02.30 AM

12.30 AM

12.40 AM

];

result:

load  mid(Time,1,Index(Time,'.')-1)&':'&if(mid(Time,Index(Time,'.')+1,2)>=30,'30','00')&' '&mid(Time,Index(Time,' ')) as assign_value,

      Time as Time1

Resident input;

Thanks,

Sreeman.

[sfatoux72]

To avoid rounding error, work directly with minutes:

LOAD Time,

           MakeTime(Hour(Time), Floor(Minute(Time), 30))

FROM Source;

[MarcoWedel]

Hi,

maybe helpful:

http://qlikviewnotes.blogspot.com/2011/10/correct-time-arithmetic.html

regards

Marco

[HirisH_V7]

Hi,

Check this,

Data:
LOAD * ,
SubField(Time,'.',1)&'.'&If(Left(Mid(Time,4,5),2)='30','30',If(Left(Mid(Time,4,5),2)='00','00','00' ))&' '&Mid(Time,7,8)as AssignValue
INLINE [
    Time
    12.00 AM
    12.14 AM
    13.12 AM
    03.30 PM
    04.00 PM
    02.30 AM
];

Fornt-end:

Hope this Helps,

PFA,

Hirish

[Anonymous]

Take a column time as Assign Time