Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Residents Join when there is no common column

Hi ,

I have 2 Residents X and Y,

 

Resident X
CategoryStartEnd
A15
B610
C1114

  

 

Resident Y
AccountAmount
1100
2101
3102
4103
5104
6105
7106
8107
9108
10109
11110
12111
13112
14113

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
CategoryAccountAmount
A1100
A2101
A3102
A4103
A5104
B6105
B7106
B8107
B9108
B10109
C11110
C12111
C13112
C14113

Can any one help me with this?

6 Replies
vinieme12
Champion III
Champion III

How to populate a sparsely populated field

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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;