-
Re: Residents Join when there is no common column
Vineeth Pujari Mar 23, 2017 8:43 AM (in response to Sandhya Sunkula)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
];
-
Re: Residents Join when there is no common column
Jens Leysen Mar 23, 2017 9:26 AM (in response to Sandhya Sunkula)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
-
Re: Residents Join when there is no common column
Vineeth Pujari Mar 23, 2017 9:29 AM (in response to Jens Leysen )writing nested if's won't be feasible if you have multiple categories
-
Re: Residents Join when there is no common column
Jens Leysen Mar 23, 2017 9:36 AM (in response to Vineeth Pujari)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.
-
-
-
Re: Residents Join when there is no common column
Dave Riley Mar 23, 2017 10:02 AM (in response to Sandhya Sunkula)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;