Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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
Keep the same name for measure as well
it will auto associate your table
or perform Join
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
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
Hey Sunny,
works with the sample data, but unfortunately not with my original Cockpit 😞
Would you be able to share an updated sample so that I can see what might be missing?
Okay, here is the original Cockpit with alienated data.
I set the labels exactly like in the sample cockpit
What is the expected output here?.... I guess there is no Kosten here... how do I calculate that?
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
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