Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
PFA
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