Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Marketlistname | Customer_CODE_Export | Delsitecode_Gridcode | Standardcontract 1 | Standardcontract 2 |
---|---|---|---|---|
201512_Mixed_0-5000 kWh_Finnish | 14183312 | 12092018_TES000 | Mix Fastpris 1 år | Mix Spot |
201512_Mixed_0-5000 kWh_Finnish | 3071008 | 12288237_TES000 | Mix Fastpris 1 år | Mix Spot |
201512_Mixed_0-5000 kWh_Finnish | 2086420 | 12052473_TES000 | Mix Fastpris 1 år | Mix Spot |
201512_Mixed_0-5000 kWh_Finnish | 2492828 | 12155947_TES000 | Mix Fastpris 1 år | Mix Spot |
201512_Mixed_5001-15000 kWh_Finnish | 3290032 | 12545099_TES000 | Mix Fastpris 2 år | Mix Spot |
201512_Mixed_5001-15000 kWh_Finnish | 14350256 | 12024388_TES000 | Mix Fastpris 2 år | Mix Spot |
201512_Wind_5001-15000 kWh_Finnish | 3496152 | 13174546_TES000 | Wind Fastpris 2 år | Wind Spot |
201512_Wind_15001- kWh_Finnish | 14324868 | 381808_JKE000 | Wind Fastpris 3 år | Wind Spot |
201601_Mixed_0-5000 kWh_Finnish | 14555280 | 2254214_DKE000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 3028702 | 2254138_TES000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 3023132 | 2264738_TES000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 3115726 | 2253977_TES000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 14923122 | 2253737_TES000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 3041628 | 2265111_TES000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 14414914 | 2253826_TES000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2365372 | 2133852_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2326530 | 2144569_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2064626 | 2145274_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 14122744 | 2118018_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2285656 | 2117904_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2132452 | 2135679_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 3495190 | 2149495_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 3442348 | 2126669_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2299690 | 2126862_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 14332696 | 2137284_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2160410 | 2061962_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2091020 | 2033389_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 14229548 | 2033614_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2160670 | 2062015_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 3145362 | 2033914_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 3573734 | 2064292_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2933254 | 2098049_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2167454 | 2063905_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 14733688 | 2074635_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2206934 | 2083363_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 3560764 | 2063600_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2102298 | 2034313_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 3143370 | 2543316_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 14036188 | 2034405_TES000 | Mix Fastpris 3 år | Mix Spot |
And this is the result im after.
Marketlistname | Customer_CODE_Export | Delsitecode_Gridcode | Standardcontract 1 | Standardcontract 2 |
---|---|---|---|---|
201512_Mixed_0-5000 kWh_Finnish | 14183312 | 12092018_TES000 | Mix Fastpris 1 år | Mix Spot |
201512_Mixed_0-5000 kWh_Finnish | 3071008 | 12288237_TES000 | ||
201512_Mixed_0-5000 kWh_Finnish | 2086420 | 12052473_TES000 | ||
201512_Mixed_0-5000 kWh_Finnish | 2492828 | 12155947_TES000 | ||
201512_Mixed_5001-15000 kWh_Finnish | 3290032 | 12545099_TES000 | Mix Fastpris 2 år | Mix Spot |
201512_Mixed_5001-15000 kWh_Finnish | 14350256 | 12024388_TES000 | ||
201512_Wind_5001-15000 kWh_Finnish | 3496152 | 13174546_TES000 | Wind Fastpris 2 år | Wind Spot |
201512_Wind_15001- kWh_Finnish | 14324868 | 381808_JKE000 | Wind Fastpris 3 år | Wind Spot |
201601_Mixed_0-5000 kWh_Finnish | 14555280 | 2254214_DKE000 | Mix Fastpris 1 år | Mix Spot |
201601_Mixed_0-5000 kWh_Finnish | 3028702 | 2254138_TES000 | ||
201601_Mixed_0-5000 kWh_Finnish | 3023132 | 2264738_TES000 | ||
201601_Mixed_0-5000 kWh_Finnish | 3115726 | 2253977_TES000 | ||
201601_Mixed_0-5000 kWh_Finnish | 14923122 | 2253737_TES000 | ||
201601_Mixed_0-5000 kWh_Finnish | 3041628 | 2265111_TES000 | ||
201601_Mixed_0-5000 kWh_Finnish | 14414914 | 2253826_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 2365372 | 2133852_TES000 | Mix Fastpris 2 år | Mix Spot |
201601_Mixed_5001-15000 kWh_Finnish | 2326530 | 2144569_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 2064626 | 2145274_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 14122744 | 2118018_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 2285656 | 2117904_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 2132452 | 2135679_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 3495190 | 2149495_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 3442348 | 2126669_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 2299690 | 2126862_TES000 | ||
201601_Mixed_5001-15000 kWh_Finnish | 14332696 | 2137284_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 2160410 | 2061962_TES000 | Mix Fastpris 3 år | Mix Spot |
201601_Mixed_15001- kWh_Finnish | 2091020 | 2033389_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 14229548 | 2033614_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 2160670 | 2062015_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 3145362 | 2033914_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 3573734 | 2064292_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 2933254 | 2098049_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 2167454 | 2063905_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 14733688 | 2074635_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 2206934 | 2083363_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 3560764 | 2063600_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 2102298 | 2034313_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 3143370 | 2543316_TES000 | ||
201601_Mixed_15001- kWh_Finnish | 14036188 | 2034405_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
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
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
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);
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;