is there any Alternate way for set analysis in load script

[SatyaPaleti]

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

[lorenzoconforti]

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;

[lorenzoconforti · Accepted solution]

Sample application attached

Lorenzo

[SatyaPaleti]

Thank you so much Lorenzo