Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Not applicable

Re: Residents Join when there is no common column

vinieme12
Not applicable

Re: Residents Join when there is no common column

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

];

jens_leysen
Not applicable

Re: Residents Join when there is no common column

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
Not applicable

Re: Residents Join when there is no common column

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

jens_leysen
Not applicable

Re: Residents Join when there is no common column

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
Not applicable

Re: Residents Join when there is no common column

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;