topic Re: Assign % to a row in App Development

Assign % to a row

agbcn — Wed, 04 Mar 2020 21:42:35 GMT

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

Re: Assign % to a row

jochem_zw — Thu, 05 Mar 2020 15:56:34 GMT

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;

Re: Assign % to a row

agbcn — Sun, 08 Mar 2020 11:39:25 GMT

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

Re: Assign % to a row

NitinK7 — Sun, 08 Mar 2020 14:44:58 GMT

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.

Re: Assign % to a row

agbcn — Sun, 08 Mar 2020 18:17:07 GMT

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,

Re: Assign % to a row

NitinK7 — Mon, 09 Mar 2020 18:18:54 GMT

Hi,

Could you provide some sample data

That will be helpful to find solution

Re: Assign % to a row

jochem_zw — Mon, 09 Mar 2020 21:16:24 GMT

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 😉

Re: Assign % to a row

agbcn — Tue, 10 Mar 2020 13:59:44 GMT

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

Re: Assign % to a row

jochem_zw — Tue, 10 Mar 2020 14:02:24 GMT

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

Re: Assign % to a row

agbcn — Tue, 10 Mar 2020 14:20:48 GMT

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

Re: Assign % to a row

jochem_zw — Tue, 10 Mar 2020 19:44:08 GMT

Something like this (attachment) will do?

Re: Assign % to a row

agbcn — Wed, 11 Mar 2020 07:25:30 GMT

Hi jochem_zw,

Thanks for your reply.

I must be doing something wrong but I can't see anything when I drop the file in Qlik Sense.

Regards

Re: Assign % to a row

jochem_zw — Wed, 11 Mar 2020 07:48:45 GMT

ah sorry it is a qlikview file. to reproduce it, create a table with Id and Days as dimension and this as the measure:

if(Days>=0 and Days<=30
,90
,if(Days>30 and Days<=60
,80
,if(Days>60 and Days<=90
,70))) etc.

Re: Assign % to a row

NitinK7 — Wed, 11 Mar 2020 08:09:04 GMT

Hi,

I think you looking like see below screenshot

use Id and days as dimension and write

expression like

if(Days>=0 and Days<=30 ,90 ,
if(Days>30 and Days<=60 ,80 ,
if(Days>60 and Days<=90 ,70 ,
if(Days>90 and Days<=120,60 ,
if(Days>120 and Days<=150,50,
if(Days>150 and Days<=180,40,
if(Days>180 and Days<=210,30)))))))

Re: Assign % to a row

agbcn — Wed, 11 Mar 2020 09:41:27 GMT

Hi,

Hardcode the intervals and % was my first idea. However, I can't hardcode the intervals as user might decide to change them pretty often. They must be read from an Excel file. Users could change the interval (from 0-30 to 0-45 for instance) and even the %. They need to do this because they need to find the best intervals and % to their business case.

Thanks for your reply and your help.

Regards

Re: Assign % to a row

jochem_zw — Wed, 11 Mar 2020 10:04:57 GMT

as long as the number of intervals is fixed, you could do it by creating an excel with the intervals. and load this excel into the model and assign variables to the interval fields, these variables you can use in your formula. The formula is fixed so the number of intervals also has to be fixed, same for the variables.

Re: Assign % to a row

agbcn — Wed, 11 Mar 2020 18:25:14 GMT

Thanks for your reply.

I'm afraid they will want to change the number of intervals 😓. Let's see if I can make them change their mind!

Regards