Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I have a following requirement. I am loading sales by country level
Load
Name,
Country,
Region,
Sales
From xyz.qvd
Here my conditions are I need to follow below rules
if Country = Australia then I have to show the Sales like Australia Sales - Poland Sales as Sales
If Country = USA then I have to show the Sales like USA Sales - Iraq Sales
If Country = Mexico then i have to Mexico Sales - Iceland Sales
We can satisfy all these conditions in Set analysis in front end but I need to do this in Script level. Can some one help me on this or can suggest me how can i get this in Script level,
You can find sample data in attachments
Thanks and Regards,
Satya
Hi
Something like this:
Data_temp:
LOAD
Name,
Country,
City,
Region,
Sales
FROM [lib://DataFolder/Test_Data.xls]
(biff, embedded labels, table is Worksheet$);
//create a sum of sales by country. I'm using group by as each country might be present twice.
//I'm renaming the Country to SecondCountry so that, when we join it back to the original table it will appear as a separate field
SumSales:
Load Country as SecondCountry,
Sum(Sales) as SecondSales
Resident Data_temp Group by Country;
//I'm joining the sum of sales with a reference table where the primary country is associated to the secondary country you need to subract sales of
left join (SumSales)
Load * inline [
Country, SecondCountry
Australia, Poland
United States, Iraq
Mexico, Iceland
];
//joining back to the original table; SecondCountry and second sales will appear as new column
Left join (Data_temp)
LOAD * Resident SumSales;
Drop table SumSales;
//calculate NetSales where a SecondSales column appears
Data:
Load *,
if(IsNull(SecondSales),Sales, (Sales - SecondSales)) as NetSales
Resident Data_temp;
drop table Data_temp;
Sample application attached
Lorenzo
Thank you so much Lorenzo