Residents Join when there is no common column

[#1]

Hi ,

I have 2 Residents X and Y,

Resident X
Category	Start	End
A	1	5
B	6	10
C	11	14

Resident Y
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

There is no common column between these two Residents, But I need to check if Account is in between Start and End of Resident X, then I should pull Category into Resident Y.

I need data like below

Final Resident Z
Category	Account	Amount
A	1	100
A	2	101
A	3	102
A	4	103
A	5	104
B	6	105
B	7	106
B	8	107
B	9	108
B	10	109
C	11	110
C	12	111
C	13	112
C	14	113

Can any one help me with this?

[vinieme12]

How to populate a sparsely populated field

[vinieme12]

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

];

[Anonymous]

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

[vinieme12]

writing nested if's won't be feasible if you have multiple categories

[Anonymous]

Indeed Vineeth, it isn't when you're working with a lot of data.

But looking at the example I don't think she is working with terabytes of data and while your solution is best practice, she might find it more usefull to have another solution that she fully understands.

[flipside]

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;