11 Replies Latest reply: Sep 22, 2017 1:48 AM by Hannes Peiffer

# Mapping Value from Dimension in another Dimension

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 :-)

• ###### Re: Mapping Value from Dimension in another Dimension

Keep the same name for measure as well

it will auto associate your table

or perform Join

• ###### Re: Mapping Value from Dimension in another Dimension

What do you mean by keeping the same name for measure?

I can't do a join as I mentioned, sorry. It has to be on the surface with expressions

• ###### Re: Mapping Value from Dimension in another Dimension

No easy way to do this... but try this

SubField(Concat(DISTINCT TOTAL Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung), ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), ',',

SubStringCount(Left(Concat(DISTINCT TOTAL Land_Bewirtung, ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), Index(Concat(DISTINCT TOTAL Land_Bewirtung, ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), Land)) &

If(Index(Concat(DISTINCT TOTAL Land_Bewirtung, ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), Land) > 0, ','), ','))

Have not tested with more data... but see if this works

• ###### Re: Mapping Value from Dimension in another Dimension

Hey Sunny,

works with the sample data, but unfortunately not with my original Cockpit :-(

• ###### Re: Mapping Value from Dimension in another Dimension

Would you be able to share an updated sample so that I can see what might be missing?

• ###### Re: Mapping Value from Dimension in another Dimension

Okay, here is the original Cockpit with alienated data.

I set the labels exactly like in the sample cockpit

• ###### Re: Mapping Value from Dimension in another Dimension

What is the expected output here?.... I guess there is no Kosten here... how do I calculate that?

• ###### Re: Mapping Value from Dimension in another Dimension

Sorry Sunny, the label "Bewirtung" (second expression) is the "Kosten" from the sample data

The goal for Country(Land) "F" is:

Mapping the "Kosten" (140) to the first Dimension (red Arrow from "F" to "F"), so that for "F" is only one line with 2.708 (Umsatz) and 140 (Bewirtung)

The same for the other Countrys

At the end I want to get rid of the second Dimension

• ###### Re: Mapping Value from Dimension in another Dimension

Try this

If(Index(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL '|' & New_CALC_Hinweis & '|', ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), '|' & New_SupplierAddressCountry & '|') > 0,

SubField(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis), ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), ':',

SubStringCount(Left(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL New_CALC_Hinweis, ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), Index(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL New_CALC_Hinweis, ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), New_SupplierAddressCountry)) &

If(Index(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL New_CALC_Hinweis, ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), New_SupplierAddressCountry) > 0, ':'), ':')))

I know you mentioned that you don't want to do any script changes, but since you were using Upper(DimensionName), my expressions are having a difficult time... just use this in the script and then you should be all good with the above expression

• ###### Re: Mapping Value from Dimension in another Dimension

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

• ###### Re: Mapping Value from Dimension in another Dimension

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

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
A1800
D100180
F250100