Time format duplicates

[Anonymous]

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

));

[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]

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]

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]

see attached

[sunny_talwar · Accepted solution]

This is strange... Try this:

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

[Anonymous]

Perfect ! Thanks