Help me on creating flags with range(LOW/MEDIUM/HIGH)

[venkatasuresh]

Hi Experts ,

Need your help on below Data flagging range(LOW/HIGH)

Data i have as input :

LOAD * INLINE [

    Country, Product_ID, Year

    Sweden, SDN-27, 08/12/2014

    Sweden, SDN-31, 10/10/2014

    Sweden, SDN-32, 13/03/2014

    UK, UK-09, 26/01/2016

    UK, UK-10, 27/01/2016

    UK, UK-11, 28/01/2016

    Sweden, SDN-28, 09/12/2014

    Sweden, SDN-29, 27/08/2014

    Sweden, SDN-30, 01/01/2014

];

Types of Priority_risk as need:

Country is consistently used on a regular basis (5 or more Product_ID per selective pried of time EX:1 year(01/01/2014 to 30/12/2014) -(Low)

Country has infrequent use (Less than 5) -(High)

Expected output:

Year	Country	Product_ID	Flag
2014	Sweden	SDN-27,SDN-31,SDN-32,SDN-28,SDN-29,SDN-30	Low
2016	Uk	UK-09,UK-10,UK-11	High

 
Thanks,

Suresh V.V

[tresesco · Accepted solution]

PFA

[HirisH_V7]

Hi Check out this,

In script side, i have picked the max count and created a flag as required,

Temp:
 LOAD *,
 Year(Year) as DataYear
 INLINE [
 
 Country, Product_ID, Year
 
 Sweden, SDN-27, 08/12/2014
 
 Sweden, SDN-31, 10/10/2014
 
 Sweden, SDN-32, 13/03/2014
 
 UK, UK-09, 26/01/2016
 
 UK, UK-10, 27/01/2016
 
 UK, UK-11, 28/01/2016
 
 Sweden, SDN-28, 09/12/2014
 
 Sweden, SDN-29, 27/08/2014
 
 Sweden, SDN-30, 01/01/2014
 ] ;
 
 Left Join
 Load DataYear,Count(Product_ID) as Count
 Resident Temp Group by DataYear,Country;
 
 left join
 Load DataYear,
 Concat(Product_ID,',') as Product_ID_New
 
 Resident Temp Group By DataYear;
 
 Picking_Max:
 Load Count
 Resident Temp;
 
 Let varMaxCount = Num(Peek('Count', 0, 'Data'));  
 
 Drop table Picking_Max ;
 
 Main_Data:
 Load *,
 If(Count<$(varMaxCount),'High','Low') as Flag
 Resident Temp;
 


 Drop Table Temp;

Hope this helps,

PFA,

Hirish