Excel Match with match_type in Qlikview

[#1]

Hi All,

Is there a way to implement the excel match function that used match_type -1,0,1 in qlikview? I am looking to find the first number that is greater than and smaller than the number in the set of numbers.

Below is the description for Excel Match function with various match_types:

Match type information

• If match_type is 1, MATCH finds the largest value that is less than or equal tolookup_value. The lookup_array must be sorted in ascending order.
• If match_type is 0, MATCH finds the first value exactly equal to lookup_value.lookup_array does not need to be sorted.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal tolookup_value. The lookup_array must be sorted in descending order.

[sunny_talwar]

May be you need to use IntervalMatch. Would you be able to share your sample data with the expected output?

[#3]

Hi Sunil,

I have 2 tables:

Table 1:

Date1: 7, 31, 61, 181, 366

Value1: 20, 30, 45, 60, 100

Table 2:

Date2: 10, 35, 60, 90, 365

Value2: 1, 3, 5, 7, 10

I want the result to be :

[sunny_talwar]

I think you missed the result to be part in the above post

[#5]

I want the result to be :

[sunny_talwar]

??

[#7]

Looks like some issue when I reply to the email. It is not getting captured.

I want the result to be:

Date: 7, 31, 61, 181, 366

Value1: 20, 30, 45, 60, 100

Value3: 1, 2.68, 5.067, 7.99, 100

[#8]

Table 1:

Date1: 7, 31, 61, 181, 366

Table 2:

Date2: 10, 35, 60, 90, 365

Value2: 1, 3, 5, 7, 10

Result Table:

Date: 7, 31, 61, 181, 366

Value3: 1, 2.68, 5.067, 7.99, 100

[#9]

Table 1:

Date1: 7, 31, 61, 181, 366

Table 2:

Date2: 10, 35, 60, 90, 365

Value2: 1, 3, 5, 7, 10

Result Table:

Date: 7, 31, 61, 181, 366

Value3: 1, 2.68, 5.067, 7.99, 100

Value3 is derived from interpolation of Value2