topic Re: Mapping Value from Dimension in another Dimension in QlikView

Mapping Value from Dimension in another Dimension

malmsteen1977 — Wed, 20 Sep 2017 12:19:30 GMT

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

dsharmaqv — Wed, 20 Sep 2017 12:32:06 GMT

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

malmsteen1977 — Wed, 20 Sep 2017 12:35:43 GMT

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

sunny_talwar — Wed, 20 Sep 2017 12:48:31 GMT

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

malmsteen1977 — Wed, 20 Sep 2017 14:39:49 GMT

Hey Sunny,

works with the sample data, but unfortunately not with my original Cockpit 😞

Re: Mapping Value from Dimension in another Dimension

sunny_talwar — Wed, 20 Sep 2017 14:42:00 GMT

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

malmsteen1977 — Wed, 20 Sep 2017 15:18:43 GMT

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

sunny_talwar — Wed, 20 Sep 2017 15:26:05 GMT

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

malmsteen1977 — Wed, 20 Sep 2017 16:05:56 GMT

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

sunny_talwar — Wed, 20 Sep 2017 17:44:03 GMT

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

swuehl — Wed, 20 Sep 2017 18:13:38 GMT

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

Re: Mapping Value from Dimension in another Dimension

malmsteen1977 — Fri, 22 Sep 2017 05:48:05 GMT

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