Residents Join when there is no common column

How to populate a sparsely populated field

temp:

Mapping Load * Inline [

Start,Category

1,A

6,B

11,C

];

Load *,if(isnull(ApplyMap('temp',Account,null())),Peek('Category'),ApplyMap('temp',Account,null())) as Category Inline [

Account,Amount

1,100

2,101

3,102

4,103

5,104

6,105

7,106

8,107

9,108

10,109

11,110

12,111

13,112

14,113

];

You don't need to create Resident X as you can create a field with your categories in the script.

For example:

Resident Y:

Load *,             // Your 2 fields Account & Amount

if(Account > 0 AND Account <= 5, 'A',

if(Account > 5 AND Account <= 10, 'B',

if(Account > 10, 'C'))) AS [Category]

Now if you want to do this dynamically with 2 residents, I would suggest you do something similar with the use of variables. Set Start and End into a variable and use them in the expression above.

Hope this helps

If working with integer whole number values, then you could use the Start and End values to build a full mapping table ...

X:
Load
Category,
Start+(iterno()-1) as ID
while iterno()<=(End-Start)+1;
Load * inline [
Category, Start, End
A, 1, 5
B, 6, 10
C, 11, 14
];

Or you could use IntervalMatch which handles all values between the Start and End values ...

X:
Load * inline [
Category, Start, End
A, 1, 5
B, 6, 10
C, 11, 14
];

Y:
Load  * inline [
Account, Amount
1.1, 100
2, 101
3.2, 102
4, 103
5, 104
6.0, 105
7, 106
8, 107
9, 108
10, 109
11, 110
12, 111
13, 112
14, 113
];

left join (Y)  IntervalMatch (Account) LOAD Start, End Resident X;
left join (Y)  Load Start, End, Category resident X;

Drop Table X;
Drop Fields Start, End;