Subtraction in Script part

[MMK]

Hi All,

Thanks in advance for your support.

I am having a table with EMP and their sales. Under employee the given data is like this

EMP Sales

Group1 1000

E1 150

E2 200

E3 100

E4 250

E5  

E6  

E7  

Uder Group1 - total 7 Emp are there. we need to show sales. But

Last 3 Employee sales details are missed we need to show last 3 employees sales as 'Others'

Others Sales = Group1 - (E1+E2+E3+E4)

I want to show in Qlik  EMP Sales Group1 1000 E1 150 E2 200 E3 100 E4 250 Other 300

 300 = 1000-(150+200+100+250). I want achieve this through backend only.

[JandreKillianRIC]

Hi @MMK 

This might be a bit of a long way around, but it works. I also added more groups as this might not always be only 1 group in the data... I wasn't sure.

Here is the resulting Table 

Here is the script 

////////////////////////////////////////////////////////////////////////////////
// Load inline with more than one group as this might be what is in the data and not
// just in the sample provided 
////////////////////////////////////////////////////////////////////////////////


TmpSales:
Load 
	*, 
    RecNo() as SortingTmp; // Adding this for the sorting in the front end
Load * Inline [
EMP,Sales
Group1,1000
E1,150
E2,200
E3,100
E4,250
E5,
E6,
E7,
Group2,1420
E1,150
E2,200
E3,100
E4,250
E5,
Group3,1970
E1,150
E2,200
E3,100
E4,250
E5,500
E6,600
E7,
E8,
E9,
];

////////////////////////////////////////////////////////////////////////////////
// Define group level per individual line item
////////////////////////////////////////////////////////////////////////////////

TmpSales2:
Load 
	SortingTmp,
	EMP, 
    IF(Sales = 0 or Len(Trim(Sales)) = 0, 0, Sales) as Sales, // Cleaning up the Sales for later
    IF(WildMatch(EMP, 'Group*'), 
    	EMP, Peek('GroupName')) as GroupName, // Getting the Group that the Sales belong to
    IF(WildMatch(EMP, 'Group*'), 
    	Sales, Peek('GroupSales')) as GroupSales // Adding the Group Totals as this is to be used later
Resident TmpSales; 

Drop Table TmpSales; 

////////////////////////////////////////////////////////////////////////////////
// Get totals per group (Excl the group values)
////////////////////////////////////////////////////////////////////////////////

GroupTotalsLineItems:
Left Join (TmpSales2)
Load 
	GroupName,  
    Sum(Sales) as TotalSales
Resident TmpSales2
Where Not WildMatch(EMP, 'Group*') // Excl the Group Value
Group By GroupName; 


////////////////////////////////////////////////////////////////////////////////
// Calculate the Others (Sales that are 0)
////////////////////////////////////////////////////////////////////////////////

TmpSales3:
Load 
    IF(Sales = 0, Peek('Sorting')+1, 
    	SortingTmp) as Sorting, // Fixing the sorting as we are grouping the Sales EMP where sales = 0
	EMP,
    IF(Sales = 0, 
    	'Others', 
        EMP) as ReportingEMP, 
    GroupName, 
    TotalSales, 
    GroupSales,
    Sales, 
    IF(Sales = 0 and Peek('ReportingEMP') <> 'Others', 
    	 GroupSales - TotalSales, 
        Sales) as ReportingSales
Resident TmpSales2
Order By SortingTmp;

Drop Table TmpSales2; 

////////////////////////////////////////////////////////////////////////////////
// Clean up table (Remove all 0's as this is catered for) 
////////////////////////////////////////////////////////////////////////////////

Sales:
NoConcatenate
Load 
	*
Resident TmpSales3
Where ReportingSales <> 0;

Drop Table TmpSales3; 

Regards - Jandre

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download