8 Replies Latest reply: Dec 28, 2016 9:30 AM by Marius August Larsen RSS

    Time format duplicates

    Marius August Larsen

      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

      [D:\QlikView\Spot Prices\1_Excel\elspot-prices_2015_hourly_nok.xlsx]

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

      ));