Assign % to a row

[agbcn]

Hi,

I got a new requirement which is driving me crazy.

I need to calculate the number of days between two dates (already did it) and taking into consideration that number of days, I have to assign a % to that row. Init date will be fixed, but end date can change considering the month of user's choice. For example, the #Days could be 30 if  end date is considered today, but it would be 10 if user choose 20 days before. The % values must be read from an Excel file (I know how to do that) so users can change them if needed. If days are between 0 and 30 days then 90 is assigned. If days are between 31 and 60, then 80 is assigned, and so on ...

To make a long story short, if the values are:

id     #Days     %

1        20          90

2        65          70

3        120       10

What is the best approach? Maybe IntervalMatch?

Thanks in advance

Regards

[jochem_zw]

Yes Intervalmatch will do, try this script:

Test:
Load * inline [
id,#Days
1,20
2,35
3,65
4,120
];

Interval_Table:
Load * Inline [
START,STOP,%
0,30,90
31,60,80
61,90,70
91,120,60
121,150,50
];

Left join(Test)
IntervalMatch(#Days)
Load START,STOP Resident Interval_Table;

Left join(Test)
Load START,STOP,% Resident Interval_Table;

Drop Table Interval_Table;

[agbcn]

Thank you very much for your reply.

I have a doubt with your solution. #Days won't be a fixed number. It will change depending on when its calculated, son I'm not sure it can be done on script.

Thanks!

Regards

[NitinK7]

Hi,

Try the following code 

I think It is useful to you

ABC:
LOAD *,
if((#days/30)>=0 and(#days/30)<=1,90,
if((#days/30)>1 and (#days/30)<=2,80,
if((#days/30)>2 and (#days/30)<=3,70,
if((#days/30)>3 and (#days/30)<=4,60,
if((#days/30)>4 and (#days/30)<=5,50))))) as Row%
;
LOAD * Inline [
id, #days
1,20
2,35
3,65
4,120
];

regards,

Nitin.

[agbcn]

Hi Nitin,

Thanks for your reply.

That was my first idea. However, the percentage must be read from an Excel file as the user may change the percentage assigned to every interval. They coud also add new percentages.

Regards,

[NitinK7]

Hi,

Could you provide some sample data

That will be helpful to find solution

[jochem_zw]

Exactly #days should be a calculated value. But I don’t have your calculated so this is example calculated data, try it an you will like it 😉

[agbcn]

Hi,

Thanks for your reply.

Here you are some data from today's calculation

Id	%	Days
1289352	10%	185
1289488	90%	2
1289570	90%	23
1289853	80%	64
1289855	90%	1
1289858	90%	2
1290002	90%	0
1290005	90%	2
1290120	10%	374

However, if calculations were done one month ago (or today but choosing a date 30 days ago), Days would change and therefore % might also change.

Let me know if you need more info.

Thanks in advance

Regards

[jochem_zw]

so you mean days are calculated at the frontend? if so, you are correct, intervalmatch won't work.

[agbcn]

Yes, days are calculated at frontend which makes it more difficult.