topic Re: how to Assign value to the rows ? in QlikView

how to Assign value to the rows ?

gauravgg — Mon, 26 Jan 2026 18:19:17 GMT

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

Re: how to Assign value to the rows ?

Gysbert_Wassenaar — Fri, 12 Feb 2016 17:55:02 GMT

Something like this:

LOAD

Time,

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

FROM

.....

Re: how to Assign value to the rows ?

sunny_talwar — Fri, 12 Feb 2016 17:56:09 GMT

Are you rounding them down to nearest 30 minutes?

May be this:

LOAD Time,

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 ?

gauravgg — Fri, 12 Feb 2016 19:09:39 GMT

Thanx

Re: how to Assign value to the rows ?

gauravgg — Fri, 12 Feb 2016 19:09:58 GMT

Thanx

Re: how to Assign value to the rows ?

sunny_talwar — Fri, 12 Feb 2016 19:33:20 GMT

Please close the thread if you got what you were looking for by marking correct and helpful answer. Thanks

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: how to Assign value to the rows ?

gauravgg — Sat, 13 Feb 2016 06:28:16 GMT

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 ?

MarcoWedel — Sat, 13 Feb 2016 09:12:59 GMT

‌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 ?

gauravgg — Sat, 13 Feb 2016 09:57:34 GMT

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 ?

sunny_talwar — Sat, 13 Feb 2016 16:44:44 GMT

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 ?

Sun, 14 Feb 2016 15:03:56 GMT

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.

Re: how to Assign value to the rows ?

sfatoux72 — Sun, 14 Feb 2016 16:41:05 GMT

To avoid rounding error, work directly with minutes:

LOAD Time,

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

FROM Source;

Re: how to Assign value to the rows ?

MarcoWedel — Sun, 14 Feb 2016 19:47:36 GMT

Hi,

maybe helpful:

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

regards

Marco

Re: how to Assign value to the rows ?

HirisH_V7 — Mon, 15 Feb 2016 05:28:17 GMT

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

Re: how to Assign value to the rows ?

Anonymous — Mon, 15 Feb 2016 06:35:28 GMT

Take a column time as Assign Time