Create range for all measures associated with each dimension

[chetansaafiya]

Hi! 

I have some huge data where I want to implement the following: I have Ids and expense/ amount associated with each Id however one Id may repeat n number of times with different or same amount each time. In load editor I want to create a range such that for member ID say 123 add up all expenses and put them in range. 

In below table for member id 123: total expense is 560 so it should lie in range of 501-600 however currently I am getting below result. 

Member Id	Expense	Range
123	90	0-100
123	150	101-200
234	200	101-200
234	45	0-100
456	350	301-400
567	257	201-300
123	320	301-400
234	380	301-400

For each id I get aggregate amount I want to create range in dimension 0-100, 101-200, 301-400 so on. Later on I want to count that for each range which Ids were there and total amount. If possible can this range be crated for each year as well.

Thanks in advance! 

[albertovarela]

Take a look at this:

[chetansaafiya]

Hi! 

Thanks for your response! I have tried this but ranges 0-100, 100-200 are required to be in dimension later on as I need to show a table with 0-100, 100-200 etc in field 1, no. of IDs in field 2, total expense in field 3. Is there any way to do that in load editor else how can I perform this task.

Thanks in advance!

[albertovarela · Accepted solution]

If you want to in the script this could be one way:

[chetansaafiya]

Thanks! It works. 

In case if I know that IDs are unique in each row hence no need to sum for all the possible cases then can I use below:

Classes:

LOAD * INLINE [

    Class, Begin, End

    0-100, 0, 100

    100-200, 100, 200

and so on

];

Inner JOIN (Classes)

IntervalMatch (Expense)

Load Distinct

Begin,

End

Resident Classes;