Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

is there any Alternate way for set analysis in load script

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

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Sample application attached

Lorenzo

View solution in original post

3 Replies
Highlighted
Specialist II
Specialist II

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;

 

Highlighted
Specialist II
Specialist II

Sample application attached

Lorenzo

View solution in original post

Highlighted
Creator III
Creator III

Thank you so much Lorenzo