topic Re: time format in QlikView

time format

tincholiver — Fri, 20 Nov 2020 20:08:08 GMT

Hi everyone, i have some problems with time format:

xls file has next header

Fecha	08:00	08:30	09:00	09:30	10:00	10:30
01/08/2020	80	5	94	20	8	52
02/08/2020	62	12	67	36	63	14
03/08/2020	43	41	19	43	42	60
04/08/2020	29	93	21	42	58	22
05/08/2020	46	39	45	39	79	3

but when i want to load i get next data

LOAD
Fecha,
[0.33333333333333331],
[0.35416666666666669],
[0.375],
[0.39583333333333298],
[0.41666666666666702],
[0.4375],
FROM...

i tried with Time and Time# function but not works.

any idea??

thanks a lot!

Re: time format

QFabian — Fri, 20 Nov 2020 22:25:09 GMT

Hi @tincholiver , here an example that i made with yor data, the words in red are the new ones :

The script transforms the Time columns in rows, using crosstable() function.

Then cratres a valid num time format, replacing a character from '.' to ','.

Aux:
CrossTable(Time, Data)
LOAD Fecha,
[0.33333333333333331],
[0.35416666666666669],
[0.375],
[0.39583333333333331],
[0.41666666666666669],
[0.4375]
FROM
[..\Desktop\Libro1.xlsx]
(ooxml, embedded labels, table is Hoja1);

Data:
Load
Fecha,
replace(Time,'.',',') as Hora,
Data
Resident Aux;
drop table Aux;

Re: time format

tincholiver — Mon, 23 Nov 2020 13:27:30 GMT

thanks Fabian!!

it works!

Regards