Mapping Value from Dimension in another Dimension

[malmsteen1977]

Hello Qlik-Guys,

Attached I have a QVW of two tables.

In the first table I sum up the revenue (Umsatz) per Country(Land) ==> first dimension

In the second table I sum up the entertainment expenses (Kosten) per Country (Land_Bewirtung) ==> second Dimension

My Goal is to map the Values from the second Dimension to the first and get rid of the Second Dimension

Example:

For the Country "D" i would like to have only one line (based on the Screenshot)

D------100 (Umsatz) -------- 180 (Sum of 30 and 150 from the second Dimension where country is "D" and Expression Values)

I only can do this in the expression, not in the script, as the expression in the original cockpit is quite difficult.

Any ideas very welcome 🙂

[swuehl]

I know that you were asking for a chart solution, but I think this kind of request should be solved in your model.

Maybe start with something like this added to your script (the first, simplified script)

Fact:

LOAD Land, Umsatz as Wert, 'Umsatz' as WertTyp

Resident Umsatz;

LOAD Land_Bewirtung as Land, Kosten as Wert, 'Kosten' as WertTyp

Resident Bewirtung;

to create a single fact table and then use WertTyp to filter your records.

Land	Sum({<WertTyp = {'Umsatz'}>}Wert)	Sum({<WertTyp = {'Kosten'}>}Wert)
	530	280
A	180	0
D	100	180
F	250	100

[malmsteen1977]

Hi Sunny,

Monster expression, but it seems to work 😉 Many thanks.

@Stefan. Also many thanks to you. I will try later if I can do a variation in the script