Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a data set that looks like this
Country Product Sales
A X 1000
A Y 100
B X 200
B Z 2000
I want to create a dimension which displays which product is the most sold per country
the result would be: product X is the most sold for country A and product Z for country B
it needs to be a dimension, not a measure, ideally I would do this in data loading the script
thank you
LM
Data:
Load * Inline
[
Country,Product,Sales
A, X, 1000
A, Y, 100
B, X, 200
B, Z, 2000
];
Temp:
Load Country, Product, SUM(Sales) as TotalSales Resident Data Group By Country, Product;
Left Join (Temp)
Load Country, Max(TotalSales) as MaxTotalSales Resident Temp Group By Country;
New:
Load Country, Product, 1 as MaxSalesFlag Resident Temp Where MaxTotalSales = TotalSales;
Drop Table Temp;
try this:
a:
load
Country,
Sum(Sales) as SalesMax
resident your_table
group by Country;
left join
load
Country,
Sales as SalesMax
Product
resident your_table;
Main_tmp:
LOAD
Country,
Product,
Sales
FROM [Cartel1.xlsx] (ooxml, embedded labels, table is Foglio1);
left join(Main_tmp)
Load
Country,
max(Sales) as BestSales
resident Main_tmp
group by Country;
Main:
NoConcatenate
Load
if(Sales=BestSales,1,0) as [Flag Most Sold],
Country,
Product,
Sales
Resident Main_tmp;
drop Table Main_tmp;
thank you, but this is too complicated for me and also of course my data structure is much more complicated than in the initial post, for example I have the same table on a monthly basis, so the question is actually "what is the most sold product per country in a certain month etc"
is there a way to calculate this in a visualization then, as a measure perhaps?
I was thinking using FirstSortedValue or something? I tried but it does not work, I get some "-" results for some records
thank you
Hi liviumac ,
Table1:
load * inline [
Country , Product , Sales
A , X , 1000
A , Y , 100
B , X , 200
B , Z , 2000
];
FinalTable:
load Country,FirstSortedValue(Product,-Sales) as MaxSoldProduct resident Table1 group by Country;
drop table Table1;
You can use MaxSoldProduct as Dimension