Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I created the below example to show my case. Lets say that I have these two inline tables:
[Subjects]:
LOAD * INLINE [
Subject, ValueOf
'Subject 123', 12
'Subject 456', 30
'Subject 789', 46
'Subject 101', 22
'Subject 111', 51
];
The above inline table contains the subjects and each subject value.
[HandelRates]:
LOAD * INLINE [
FromValue, ToValue, Rate
1, 25, 1
26, 40, 2
41, 60, 3
61, 80, 4
81, 100, 5
];
the above inline table contains the rates of subjects. so in case the subject X has value 15 so its rate is 1 because 15 is between 1 and 25.
So I need to have a chart (table) as shown below:
with each "Subject" I should show the "Rate" based on the subject's "ValueOf". If the "ValueOf" is between the "FromValue" and "ToValue" then show the "Rate".
I added the sample in the attachments.
any one can help me with this? please advice.
Dear,
Can ValueList or ValueLoop fix my issue?
Awaiting for your kind help and support.
Hi,
Try like this
HandelRates:
LOAD * INLINE [
FromValue, ToValue, Rate
1, 25, 1
26, 40, 2
41, 60, 3
61, 80, 4
81, 100, 5
];
Subjects:
LOAD * INLINE [
Subject, ValueOf
'Subject 123', 12
'Subject 456', 30
'Subject 789', 46
'Subject 101', 22
'Subject 111', 51
];
Chart : Straight Table
Dimension: Subject, ValueOf
Expression: =Aggr(If([ValueOf] >= FromValue AND [ValueOf] <= ToValue, Rate), FromValue, ToValue, Subject)
But if possible handle this in scirpt by using IntervalMatch() otherwise you will get performance issues if data is huge.
Hope this helps you.
Regards,
jagan.