Needs help in loop within chart

[mohammedsleem]

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.

[mohammedsleem]

Dear,

Can ValueList or ValueLoop fix my issue?

Awaiting for your kind help and support.

[jagan · Accepted solution]

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.