14 Replies Latest reply: Feb 15, 2016 1:35 AM by V SUDHAKAR

# how to  Assign value to the rows ?

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

• ###### Re: how to  Assign value to the rows ?

Something like this:

Time,

Time(Floor(Time,1/48)) As [Assign Value]

FROM

.....

• ###### Re: how to  Assign value to the rows ?

Thanx

• ###### Re: how to  Assign value to the rows ?

Are you rounding them down to nearest 30 minutes?

May be this:

Time(Floor(Time, (1/(24*2)))) as AssignValue -> my bad, Gysbert is right

Time(Floor(Time, (1/(24*30)))) as AssignValue

FROM Source;

• ###### Re: how to  Assign value to the rows ?

Thanx

• ###### Re: how to  Assign value to the rows ?

Qlik Community Tip: Marking Replies as Correct or Helpful

• ###### Re: how to  Assign value to the rows ?

hi

but for 8.00, 8.15, 8.30  it is assigning 8.00

it should not assign 8.30 to 8.00

• ###### Re: how to  Assign value to the rows ?

please provide an example where the proposed expression converts 8.00 into 8.30

thanks

regards

Marco

• ###### Re: how to  Assign value to the rows ?

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

12.30 AM

12.40 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

12.30 AM                     12.30 AM

12.40 AM                      12.30 AM

.

n numbers of rows

• ###### Re: how to  Assign value to the rows ?

Time component is in decimals so it is prone to rounding errors when you round, ceil or floor things. Check this link out where HIC talks about rounding error Rounding Errors.

• ###### Re: how to  Assign value to the rows ?

To avoid rounding error, work directly with minutes:

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

FROM Source;

• ###### Re: how to  Assign value to the rows ?

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:

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.

• ###### Re: how to  Assign value to the rows ?

Hi,

Check this,

```Data:
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

• ###### Re: how to  Assign value to the rows ?

Take a column time as Assign Time