Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
Very simple solution, thanks a lot!