Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Take a look at this:
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!
If you want to in the script this could be one way:
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;