Using field selections and concat to bring in data from other tables
How would you structure this data?
If the selection of Countries do not make up a record in CountryGroup by using concat, then produce a simple pivot table using the expression Count().(Table A)
If the selections produce a concat string that matches the CountryGroup string then format the table CountryGroup and Target fields but add an expression to count the product IDs (Table B)
I can't get to a point where concat of Country will match Countrygroup and change the format of the table, although the table in the middle of the qvw the concat formula is built..
Also when you click on partial sum in a pivot table, can you change the 'Total' label to better reflect the selection, for example I would rather it read total of individual transactions than Total.
How do you want to handle user selections of only a subset of the CountryGroup, e.g. UK and Wales? Or only Wales, which belongs to both CountryGroups?
In general, if you want to directly compare a CountryGroup value with the concatenated Country selections, the concatenated substring needs to be sorted identically to the CountryGroup. If you don't use a sort weight parameter in the Concat() function, the substrings will be sorted alphabetically (and your CountryGroup is not constructed like that).
Hence, if you want to follow your approach, either re-construct your CountryGroup values or define a sort weight in the Concat() function.
I have managed to get a bit further on this, by using a link table but not joining it to the main table I can make one list and then use the country list to make the selections.
This means the pivot table can 'look at' the country list and use the concat function.
So I can then make an if statement to look at if the selection has a target.
But I can't figure what happens if a multiple selection is not in the list.
(I have marked the pivot table in the QVW wherre I am having issues)
The first 2 columns return a figure if there is a country for concat selection in list C2 or a single country selection.
In column 3 I need to count product ID's where a multiple selection is not in the list C2.
So I am using the set expression p() but I cant link Country to C2 selection when Country selection is not in the list C2.
The reason is thier could be targets on some countries and some groups of countries.
Therefore if thier is a single selection without a target just count productID
If the single selection has a target, show the target and the count.
If thier is more than one selection from the country and there is a match in the countrygroup or C2( a concatenated list of country and countrygroup). Then count the productID's and bring through the target.
If not the count the productIDs of the multiple countries selected.
So I need all the countries and groups in one list to achieve this.
If I exclude single countries from C2 how can I make all my points above?
I think I still haven't fully understood what you are trying to achieve. Your logic could get quite complex when there are user selections in field Country that partly match C2 and partly doesn't.
If thier is more than one selection from the country and there is a match in the countrygroup or C2( a concatenated list of country and countrygroup). Then count the productID's and bring through the target.
If not the count the productIDs of the multiple countries selected.
Maybe you want to consider changing your data model, maybe with a table of mixed granularity.
I have uploaded a new model, the concat formula brings back exclusions I dont need that. I just need the CGcountry concat string to find any match in the Countrygroup1 field.
if so bring back the target, I cant belive this basic operation is not straight forward, really frustrated.
