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

Excel Lookup SYNTAX - Script

He Guys!

I need some help for my script:

Excel-Syntax:

=LOOKUP(A1;{0.51.151};{1,5.2.3,75})

Whats the syntax for my { }-condition?

Thank you!

LG

Michael

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Mapping:

Mapping

LOAD * Inline [

KM, Number

0, 1.5

51, 2

151, 3.75

];

Table:

LOAD KM,

  ApplyMap('Mapping', If(KM < 50, 0, If(KM < 150, 51, 151))) as Number;

LOAD * Inline [

KM

100

34

60

190

134

24

];

Output:

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

You are seeking help with Excel or QlikView?

vikasmahajan

you need to give applymap function in Qlikview.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

For QlikView of corse

applymap function sounds ok.

Do you have an example for me?

sunny_talwar

Not applicable
Author

Thanks. Thought about this already.

But an applymap and Inline Table only find exact matches.

But in my case i have to find a special number if km is between two values.

Here is the example:

Unbenannt.PNG

In this  case, the result = 2.

----------------------

QlikView Script:

Mapping load * inline [

km, number

0, 30

51, 40

151, 50

];

With an applymap i can only find the number to the exact km, or?

In this case, the Number is 100 -> incorrect

Only with km 0, 51 & 151 there is e correct match.

I hope I don’t  think too complicated

Not applicable
Author

Sorry for no confusion:


Mapping load * inline [

km, number

0, 1.5

51, 2

151, 3.75

];

hic
Former Employee
Former Employee

You should look at Intervalmatch. See IntervalMatch

HIC

sunny_talwar

Try this:

Mapping:

Mapping

LOAD * Inline [

KM, Number

0, 1.5

51, 2

151, 3.75

];

Table:

LOAD KM,

  ApplyMap('Mapping', If(KM < 50, 0, If(KM < 150, 51, 151))) as Number;

LOAD * Inline [

KM

100

34

60

190

134

24

];

Output:

Capture.PNG

Not applicable
Author

Yea, thank you.

At the end it seems so clear an easy