Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

4 Replies

Re: Keep only first row of data for some columns

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

Not applicable

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

(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

MVP
MVP

Re: Keep only first row of data for some columns

You can also try to make use of EXISTS() function (take care that Marketlistname hasn't loaded before):

LOAD Marketlistname,

     Customer_CODE_Export,

     Delsitecode_Gridcode,

     if(not exists(Marketlistname), [Standardcontract 1]) as [Standardcontract 1],

     if(not exists(Marketlistname), [Standardcontract 2]) as [Standardcontract 2]

FROM

[https://community.qlik.com/thread/199079]

(html, codepage is 1252, embedded labels, table is @1);

Not applicable

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

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

Community Browser