4 Replies Latest reply: Jan 11, 2016 10:53 AM by Niclas Söderberg RSS

    Keep only first row of data for some columns

      Hi,

       

      Im looking for a way to export a table and keep only the first row of data for some of the columns. I

      For columns "Standardcontract 1" and "Standardcontrcat 2" id lik only to have data on the first occurrence of "Market list name"

       

      This is how it looks today.

       

      MarketlistnameCustomer_CODE_ExportDelsitecode_GridcodeStandardcontract 1Standardcontract 2
      201512_Mixed_0-5000 kWh_Finnish1418331212092018_TES000Mix Fastpris 1 årMix  Spot
      201512_Mixed_0-5000 kWh_Finnish307100812288237_TES000Mix Fastpris 1 årMix  Spot
      201512_Mixed_0-5000 kWh_Finnish208642012052473_TES000Mix Fastpris 1 årMix  Spot
      201512_Mixed_0-5000 kWh_Finnish249282812155947_TES000Mix Fastpris 1 årMix  Spot
      201512_Mixed_5001-15000 kWh_Finnish329003212545099_TES000Mix Fastpris 2 årMix  Spot
      201512_Mixed_5001-15000 kWh_Finnish1435025612024388_TES000Mix Fastpris 2 årMix  Spot
      201512_Wind_5001-15000 kWh_Finnish349615213174546_TES000Wind Fastpris 2 årWind Spot
      201512_Wind_15001- kWh_Finnish14324868381808_JKE000Wind Fastpris 3 årWind Spot
      201601_Mixed_0-5000 kWh_Finnish145552802254214_DKE000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish30287022254138_TES000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish30231322264738_TES000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish31157262253977_TES000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish149231222253737_TES000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish30416282265111_TES000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish144149142253826_TES000Mix Fastpris 1 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish23653722133852_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish23265302144569_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish20646262145274_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish141227442118018_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish22856562117904_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish21324522135679_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish34951902149495_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish34423482126669_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish22996902126862_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish143326962137284_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_15001- kWh_Finnish21604102061962_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish20910202033389_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish142295482033614_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish21606702062015_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish31453622033914_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish35737342064292_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish29332542098049_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish21674542063905_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish147336882074635_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish22069342083363_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish35607642063600_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish21022982034313_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish31433702543316_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish140361882034405_TES000Mix Fastpris 3 årMix  Spot

       

       

      And this is the result im after.

       

       

      MarketlistnameCustomer_CODE_ExportDelsitecode_GridcodeStandardcontract 1Standardcontract 2
      201512_Mixed_0-5000 kWh_Finnish1418331212092018_TES000Mix Fastpris 1 årMix  Spot
      201512_Mixed_0-5000 kWh_Finnish307100812288237_TES000
      201512_Mixed_0-5000 kWh_Finnish208642012052473_TES000
      201512_Mixed_0-5000 kWh_Finnish249282812155947_TES000
      201512_Mixed_5001-15000 kWh_Finnish329003212545099_TES000Mix Fastpris 2 årMix  Spot
      201512_Mixed_5001-15000 kWh_Finnish1435025612024388_TES000
      201512_Wind_5001-15000 kWh_Finnish349615213174546_TES000Wind Fastpris 2 årWind Spot
      201512_Wind_15001- kWh_Finnish14324868381808_JKE000Wind Fastpris 3 årWind Spot
      201601_Mixed_0-5000 kWh_Finnish145552802254214_DKE000Mix Fastpris 1 årMix  Spot
      201601_Mixed_0-5000 kWh_Finnish30287022254138_TES000
      201601_Mixed_0-5000 kWh_Finnish30231322264738_TES000
      201601_Mixed_0-5000 kWh_Finnish31157262253977_TES000
      201601_Mixed_0-5000 kWh_Finnish149231222253737_TES000
      201601_Mixed_0-5000 kWh_Finnish30416282265111_TES000
      201601_Mixed_0-5000 kWh_Finnish144149142253826_TES000
      201601_Mixed_5001-15000 kWh_Finnish23653722133852_TES000Mix Fastpris 2 årMix  Spot
      201601_Mixed_5001-15000 kWh_Finnish23265302144569_TES000
      201601_Mixed_5001-15000 kWh_Finnish20646262145274_TES000
      201601_Mixed_5001-15000 kWh_Finnish141227442118018_TES000
      201601_Mixed_5001-15000 kWh_Finnish22856562117904_TES000
      201601_Mixed_5001-15000 kWh_Finnish21324522135679_TES000
      201601_Mixed_5001-15000 kWh_Finnish34951902149495_TES000
      201601_Mixed_5001-15000 kWh_Finnish34423482126669_TES000
      201601_Mixed_5001-15000 kWh_Finnish22996902126862_TES000
      201601_Mixed_5001-15000 kWh_Finnish143326962137284_TES000
      201601_Mixed_15001- kWh_Finnish21604102061962_TES000Mix Fastpris 3 årMix  Spot
      201601_Mixed_15001- kWh_Finnish20910202033389_TES000
      201601_Mixed_15001- kWh_Finnish142295482033614_TES000
      201601_Mixed_15001- kWh_Finnish21606702062015_TES000
      201601_Mixed_15001- kWh_Finnish31453622033914_TES000
      201601_Mixed_15001- kWh_Finnish35737342064292_TES000
      201601_Mixed_15001- kWh_Finnish29332542098049_TES000
      201601_Mixed_15001- kWh_Finnish21674542063905_TES000
      201601_Mixed_15001- kWh_Finnish147336882074635_TES000
      201601_Mixed_15001- kWh_Finnish22069342083363_TES000
      201601_Mixed_15001- kWh_Finnish35607642063600_TES000
      201601_Mixed_15001- kWh_Finnish21022982034313_TES000
      201601_Mixed_15001- kWh_Finnish31433702543316_TES000
      201601_Mixed_15001- kWh_Finnish140361882034405_TES000

       

      Do you have a sollution for this? It doesnt need to be for export only I could also change the table before export of that is an alternative.

       

      Br
      Niclas

        • Re: Keep only first row of data for some columns
          Marcus Sommer

          Within the script you could with peek/previous access and therefore check a previous record and change the actual record appropriate: Peek() or Previous() ? Within a table-chart you could use above/before in a similar way: The Above Function.

           

          But what is the reason? To save a few bytes by storing and/or a better overview within the chart (I doubt it would be better)?

           

          - Marcus

            • Re: Keep only first row of data for some columns

              Hi,


              The reason is that .csv file will be imported into another system and the specification i ask for is based on that one.

              I will try to read up on Peak function.

               

              This is how the script looks today. If someone has time to try to include peak into this one it would be appreciated.

               

               

              Marketlist2:

              load

              DUEDATEYYYYMM_Export&text('_')&ENERGI_ORIGIN_Export&text('_')&EYC_Group_Export&text('_')&Customer_LANGUAGE_NAME_Export as Marketlistname,

                ENERGI_ORIGIN_Export&text('_')&EYC_Group_Export as Campaigndefinition,

                Customer_CODE_Export,

                DELIVERYSITE_CODE_Export&text('_')&GRIDAREA_CODE_Export as Delsitecode_Gridcode

              Resident tmpExportLink

              where 1=1

              and DUEDATEYYYYMM_Export <= $(vToday2)

              ;

               

               

              left join(Marketlist2)

              LOAD Campaigndefinition,

                   [Standardconctract 1],

                   [Standardcontract 2]

              FROM

              [C:\XXXXXXXXXXXXXXXX\Campaign offer definition.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

               

               

               

              Marketlist:

              load

                Marketlistname,

                Customer_CODE_Export,

                Delsitecode_Gridcode,

                Campaigndefinition,

                [Standardconctract 1],

                [Standardcontract 2],

                1 as _tmponly

              Resident Marketlist2

              order by Marketlistname

              ;

               

               

              drop field Campaigndefinition;

              drop field _tmponly;

              drop Table Marketlist2;

               

              Br
              Niclas

            • Re: Keep only first row of data for some columns

              Hi,

               

              Solved the problem. Maybe not according to best practice

               

               

              Marketlist2:

              load

                DUEDATEYYYYMM_Export&text('_')&ENERGI_ORIGIN_Export&text('_')&EYC_Group_Export&text('_')&Customer_LANGUAGE_NAME_Export as Marketlistname,

                ENERGI_ORIGIN_Export&text('_')&EYC_Group_Export as Campaigndefinition,

                Customer_CODE_Export,

                DELIVERYSITE_CODE_Export&text('_')&GRIDAREA_CODE_Export as Delsitecode_Gridcode

              Resident tmpExportLink

              where 1=1

              and DUEDATEYYYYMM_Export <= $(vToday2)

              ;

               

               

               

               

              Marketlist:

              load

                RowNo() as RowNumber,

                Marketlistname,

                Customer_CODE_Export,

                Delsitecode_Gridcode,

                Campaigndefinition,

                1 as _tmponly

              Resident Marketlist2

              where 1=1

              and WildMatch(Marketlistname, '201601_Mixed*')

              order by Marketlistname

              ;

               

               

               

               

              MinRowNumber:

              load

                Campaigndefinition as Campaigndefinition2,

                min(RowNumber) as minRowNumber

              Resident Marketlist

              group by Campaigndefinition;

               

               

               

               

              left join(MinRowNumber)

              LOAD Campaigndefinition as Campaigndefinition2,

                   [Standardcontract 1],

                   [Standardcontract 2]

              FROM

              [C:\XXXXXXXXXXXXXXXXXX\Campaign offer definition.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

               

               

               

              left join(Marketlist)

              load

                minRowNumber as RowNumber,

                [Standardcontract 1],

                [Standardcontract 2]

              Resident MinRowNumber;

               

               

               

               

              drop Table Marketlist2;

              drop Table MinRowNumber;

              drop field Campaigndefinition;

              drop field _tmponly;

              drop Field RowNumber;

               

               

               

               

              store Marketlist into Marketlist.csv (txt);

              drop table Marketlist;