Percentage seggregation based on ID field

[rajinikanth]

Hi All,

I have data as below

Load * inline

[

ID,Region,complete_Date

IB11,asia,1/jan/2018

IB2,asia,2/feb/2018

IB3,asia,4/feb/2018

IB4,asia,6/mar/2018

IB5,usa,9/feb/2019

IB6,usa,12/feb/2019

IB7,usa,14/mar/2019

];

Expected OutPut

When i select asia  i need the date when 75 % of my asias IDs are going to be completed.(output 4/feb/2018)

When i select usa i need the date when 100 % of my usa  IDs are going to be completed.(output 14/mar/2018)

[vishsaggi]

Can you elaborate why you have to get output as 4/feb/2018? And how did you calculate 75% will be completed?

[rajinikanth]

Yeah its an oral calculation

Say i have 10 ids 50 percent of 10 ids is 5 so i need the date on 5th id

Basically a rownum would work here i guess

[vishsaggi]

Ok will check it.

[vishsaggi]

Try this?

Aggr:

LOAD *, AutoNumber(Region&ID1, Region) AS RowNum;

Load ID1, Trim(Region) AS Region,complete_Date, Date#(complete_Date, 'DD/MMM/YYYY') AS DateID inline

[

ID1,Region,complete_Date

IB11,asia,1/jan/2018

IB2,asia,2/feb/2018

IB3,asia,4/feb/2018

IB4,asia,6/mar/2018

IB5,usa,9/feb/2019

IB6,usa,12/feb/2019

IB7,usa,14/mar/2019

];

Then add straight table object.

Add Dim

Region, ID1

Expr:

= Pick(Match(Region, 'asia', 'usa'), IF(RowNum = (75/100)*Count(TOTAL  {<Region = {'asia'}>} ID1), complete_Date),

                                     IF(RowNum = Count(TOTAL {<Region = {'usa'} >} ID1), complete_Date))

You should see below: