Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lookup problem

Hi fellow developers,

I'm trying to do the following lookup in excel.

PrincipalBand
20
1212
1818
Band
0
10
20

I have a table that has the principal amount, and another table w/ the band values. I'm trying to create the 1st table. The basic logic is we're using principal as the lookup value against the band table to map the corresponding lookup value. Instead of doing the exact lookup, we're interested in finding the closest match. In other words Principal value of 2 is closer to band 0 than band 10 and hence belongs to Band 0. Similarly Principal value 12 is closer to band 10 than band 20, and therefore belongs to band 12.

Hope this made sense. How can accomplish this type of lookup in QlikView.

Thanks in advance.

Regards

V

1 Solution

Accepted Solutions
Not applicable
Author

Unfortunately not the shortest solution. Suitable at the model level.

VLookUpSource:
LOAD * INLINE [
Principal,    Band
2,    0
12,    12
18,18
]
;
T:
LOAD * INLINE [
Something
0
10
20
]
;
Left Join(T)
LOAD
    Band
Resident VLookUpSource
;

NoConcatenate
T2:
load
Something,
FirstSortedValue(Band, Diff)
Group By Something
;
LOAD
    Something,
    Band,
    fabs(Something-Band) as Diff
Resident T
;

DROP Table T;

View solution in original post

3 Replies
Not applicable
Author

Unfortunately not the shortest solution. Suitable at the model level.

VLookUpSource:
LOAD * INLINE [
Principal,    Band
2,    0
12,    12
18,18
]
;
T:
LOAD * INLINE [
Something
0
10
20
]
;
Left Join(T)
LOAD
    Band
Resident VLookUpSource
;

NoConcatenate
T2:
load
Something,
FirstSortedValue(Band, Diff)
Group By Something
;
LOAD
    Something,
    Band,
    fabs(Something-Band) as Diff
Resident T
;

DROP Table T;

rwunderlich

Are your band values in increments of 10? If so, you could use

round(Principal, 10)

-Rob

Not applicable
Author

Thanks everyone for your input. This really helped.