topic Re: Time format duplicates in QlikView

Time format duplicates

Anonymous — Wed, 28 Dec 2016 11:57:54 GMT

Hi,

am trying to join some excelfiles, but the file i download from internet has a different time format than the file i have loaded in from desktop.

therefore i get duplicate values in my hours table.

script:

LOAD Date as %DateKey,

time#(Hours, 'hh-hh') as Hours,

SYS,

SE1,

SE2,

SE3,

SE4,

Oslo,

Kr.sand,

Bergen,

Molde,

Tr.heim,

Tromsø

FROM

(ooxml, embedded labels, table is [elspot-prices_2015_hourly_nok]);

join

LOAD

//Date(F1, 'DD.MM.YYY') as %DateKey,

date(date#(F1,'DD-MM-YYYY'),'DD.MM.YYYY') as %DateKey,

time#(Hours, 'hh-hh') as Hours,

SYS,

SE1,

SE2,

SE3,

SE4,

Oslo,

Kr.sand,

Bergen,

Molde,

Tr.heim,

TromsÃ¸ as Tromsø

FROM

[http://www.nordpoolspot.com/globalassets/marketdata-excel-files/elspot-prices_2016_hourly_nok.xls]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 1))

));

Re: Time format duplicates

sunny_talwar — Wed, 28 Dec 2016 12:24:21 GMT

I don't think hh-hh is a format which can be specified using the Time#() function. I think this is still read as text. Do you want to load them as time? Not sure how this is going to be used? If text is okay, may be use Trim() function

Trim(Hours) as Hours

Re: Time format duplicates

Anonymous — Wed, 28 Dec 2016 12:37:13 GMT

Thanks for reply

It shows from hour to hour.

Like 00-01, 01-02, 02-03, 03-04....

Trim(Hours) as Hours gives me the same result

do you have some other examples i can try ? i have been stuck at this for quite some time now...

Re: Time format duplicates

sunny_talwar — Wed, 28 Dec 2016 12:39:44 GMT

How about this:

Num(Trim(SubField(Hours, '-', 1)), '00') & ' - ' & Num(Trim(SubField(Hours, '-', 2)), '00') as Hours

Re: Time format duplicates

Anonymous — Wed, 28 Dec 2016 12:53:18 GMT

Still the same problem...

How can this be so difficult?

i have 3 files joined in the application

1: all spot prices from 2014 (excelfile)

2: all spot prices from 2015 (excelfile)

3: all spot prices from 2016 (html) web file

the problem came when i loaded the price table from the web page because its a different format, but the same text/numbers.

will it help to attached the QVW file?

Re: Time format duplicates

sunny_talwar — Wed, 28 Dec 2016 12:57:57 GMT

Yes, I think the qvw will be helpful to look at

Re: Time format duplicates

Anonymous — Wed, 28 Dec 2016 13:00:19 GMT

see attached

Re: Time format duplicates

sunny_talwar — Wed, 28 Dec 2016 13:11:55 GMT

This is strange... Try this:

Left(KeepChar(Hours, '0123456789'), 2) & ' - ' & Right(KeepChar(Hours, '0123456789'), 2) as Hours

Re: Time format duplicates

Anonymous — Wed, 28 Dec 2016 14:30:49 GMT

Perfect ! Thanks