split one row in two under a condition

[Lohmie]

Hi all,

I have a list of fuel-consumption which looks as follows:

Fuel	year	consumption [L]
Diesel	2019	6
Benzine	2020	50
B7 Diesel	2021	100
B30 Diesel	2020	200
B7 Benzine	2018	300
…

The B fuels indicate a blend consisting of a biofuel and a regular fuel (Number indicates % of Biofuel). I need to visualize how much biofuel was used in a certain year compared to regular fuel. But for this I first need to split up the blends, since these consist of both regular and bio fuel. The finale table should look like this:

Fuel	year	consumption [L]
Diesel	2019	6
Benzine	2020	50
B7 Diesel regular part	2021	93
B7 Diesel Bio part	2021	7
B30 Diesel regular part	2020	140
B30 Diesel Bio part	2020	60
B7 Benzine regular part	2018	279
B7 Benzine Bio part	2018	21
…	…	…

So a row with 100 L of B7 Diesel was split up into two rows, one B7 Diesel regular with 93 L and one B7 Diesel Bio with 7 L.

I also created a table like this specifying the blend characteristics:

Blendname:	Fuel 1	%Fuel1	Fuel 2	%Fuel2
B7 Diesel	Bio	7	Diesel	93
B30 Diesel	Bio	30	Diesel	70
B7 Benzine	Bio	7	Benzine	93
…	…	…	…	…

I am new in qlik sense and this is a bit above my capabilities. If anyone can offer help I would appreciate it!

[Vegar · Accepted solution]

What if you  start of with something like this:

Load Blendname as Fuel, [Fuel 1] as Blend , %Fuel1 as Blend%

from BlendCharacter;

Concatenate Load Blendname as Fuel, [Fuel 2] as Blend , %Fuel2 as Blend%

from BlendCharacter;

Join 

load Fuel, Year, [Consumption] 

From ConsumptionData;

After this join you will have the fields and data you need to calculate the amounts (Consumption  * Blend%) and to generate the correct Blend name ( Fuel &' '& Blend)

I hope this was helpful and guided you into one possible direction.

[Lohmie]

Very simple solution, thanks a lot!