Create status using two different fields

[ms_12]

Hi All,

I have attached excel file which contains data and requirement. I want to create a report with status and there is logic to create status in attached file. I have also attached screenshot 

[Qrishna]

1st table:
Dim: Country
Measures1: Amount =sum(Amount)
Measures2: Amount_Sum =Num(sum(Amount)/sum(total Amount), '##.##%')
Measures3: Accum =Num(Rangesum(Above(Amount_Sum,0,RowNo())), '##.##%')

2nd table:
Dim: Item
Measures1: Amount =sum(Amount)
Measures2: Amount_Sum =Num(sum(Amount)/sum(total Amount), '##.##%')
Measures3: Accum =Num(Rangesum(Above(Amount_Sum,0,RowNo())), '##.##%')

3rd table:
Dim: Combined
Measure: Amount =sum(Amount)

[ms_12]

@Qrishna Thanks.Let me clarify. In data I have only 3 columns Country, Item and Amount.  Then I need to create 3 status field for which I have explained the logic. My end goal is to create Report highlighted for which I need to create status field

[Qrishna]

put this in your load editor to create new fields and on the UI everything remains same as from my above post:

Directory;
LOAD *, [Group By Country Status] & [Group By Item Status] as Combined;
LOAD
Country,
Item,
Amount
, pick(match(Country, 'ABC', 'DEF', 'PQR', 'XYZ'), 'A', 'A', 'B', 'B') as [Group By Country Status] //use when you want to explicitly assign
, pick(match(Item, 'aa', 'ab', 'ac'), 'A', 'A', 'B') as [Group By Item Status]

// , if(match(Country, 'ABC', 'DEF'), 'A', 'B') as [Group By Country Status]
// , if(match(Item, 'aa', 'ab')), 'A', 'B') as [Group By Item Status] //use when you have few values, this can used fr many assignments too, use at your own methodoly

// ,[Group By Country Status]
// ,[Group By Item Status]
// ,Combined
FROM
[2491777 - Create status using two different fields (1).xlsx]
(ooxml, embedded labels, table is Sheet1)
where not isnull(Country);

[Kushal_Chawda · Accepted solution]

@ms_12  try below. Assuming your Item are aa,ab,ac & ad. You have two ac which looks odd

Data:
Load *
Inline [
Country	Item 	Amount
ABC	aa	50
ABC	ab	15
ABC	ac	12
ABC	ad	12	
DEF	aa	25
DEF	ab	10
DEF	ac	8
DEF	ad	7	
PQR	aa	10
PQR	ab	7
PQR	ac	5
PQR	ad	3	
XYZ	aa	4
XYZ	ab	3
XYZ	ac	2
XYZ	ad	1
](delimiter is '\t');

Country:
Load Distinct Country,
     Window(sum(Amount),Country)/Window(sum(Amount)) as perc_by_country
Resident Data;

Left Join(Data)
Load *,
     RangeSum(peek('accum_perc_by_country'),perc_by_country) as accum_perc_by_country
Resident Country
Order by perc_by_country desc;

Drop Table Country;

Item:
Load Distinct Item,
     Window(sum(Amount),Item)/Window(sum(Amount)) as perc_by_Item
Resident Data;

Left Join(Data)
Load *,
     RangeSum(peek('accum_perc_by_Item'),perc_by_Item) as accum_perc_by_Item
Resident Item
Order by perc_by_Item desc;

Drop Table Item;

Final:
Load *,
     if(accum_perc_by_country<=0.8,'A','B') & if(accum_perc_by_Item<=0.8,'A','B') as Combined_status
Resident Data;

Drop Table Data;

[ms_12]

@Kushal_Chawda  Indeed it was four different Item. Just a typo. Thanks