Fill all holes in a Time collumn

[Anonymous]

Hello,

I am extracting only the Time from a DateTime collumn through this code line:

to_char(l.dat_transacao, 'hh24:mi:ss' ) as "Horário"

My table is like this:

Horário	Valor
16:36:16	5
16:36:21	3
16:36:23	6

But I want all the seconds to be shown. How can I get the table to be like this?

Horário	Valor
16:36:16	5
16:36:17	null
16:36:18	null
16:36:19	null
16:36:20	null
16:36:21	3
16:36:22	null
16:36:23	6

EDIT: This table is used to generate a line chart. If I can add a custom dimension with all the seconds it's already a solution.

[olivierrobin]

hello

why don't you create a table with all time values first

and then join your sql request on key time ?

[sasiparupudi1]

may be Try a custom expression and drag it to the front.. You need a bit of an hack to get this work

if you define this as an expression , you can change the format into time using the numebr tab

ValueLoop(Time#('16:36:16','hh:mm:ss'),Time#('16:36:23','hh:mm:ss'),MakeTime(0,0,1))

if not, pl post a sample app

[PunamWagh]

Try this,

Data:

load Sales,

Timestamp#(date_time,'MM/DD/YY hh:mm:ss') as date_time;

Load * inline [

date_time,Sales

03/12/18 16:36:16, 20

03/12/18 16:36:23, 40

03/12/18 16:36:21, 10

];

// Master Caendar

Let vMinDate = floor(Timestamp#('03/12/18 16:36:16','MM/DD/YY hh:mm:ss')); 

Let vMaxDate = floor(Timestamp#('03/12/18 16:36:23','MM/DD/YY hh:mm:ss')); 

CalendarTemp: 

LOAD   

     TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'MM/DD/YY hh:mm:ss') AS date_time,

     TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'hh:mm:ss') AS time

AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  

At front end you can add calculated dimension as

=if(date_time <= '03/12/18 16:36:23' and date_time >= '03/12/18 16:36:16',time)