Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

malmsteen1977
New Contributor

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

1 Solution

Accepted Solutions
MVP
MVP

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

Capture.PNG

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

11 Replies
dsharmaqv
Contributor III

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

malmsteen1977
New Contributor

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

MVP
MVP

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

malmsteen1977
New Contributor

Re: Mapping Value from Dimension in another Dimension

Hey Sunny,

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

MVP
MVP

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?

malmsteen1977
New Contributor

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

MVP
MVP

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?

malmsteen1977
New Contributor

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 DimensionQlik(2).jpg

MVP
MVP

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

Capture.PNG

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