Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Time format duplicates

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))

));

1 Solution

Accepted Solutions
sunny_talwar

This is strange... Try this:

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

View solution in original post

8 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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...

sunny_talwar

How about this:

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

Anonymous
Not applicable
Author

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?

sunny_talwar

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

Anonymous
Not applicable
Author

see attached

sunny_talwar

This is strange... Try this:

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

Anonymous
Not applicable
Author

Perfect ! Thanks