Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Excel Lookup SYNTAX - Script

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

10 Replies
sunny_talwar
Not applicable

Re: Excel Lookup SYNTAX - Script

You are seeking help with Excel or QlikView?

vikasmahajan
Not applicable

Re: Excel Lookup SYNTAX - Script

you need to give applymap function in Qlikview.

Vikas

Not applicable

Re: Excel Lookup SYNTAX - Script

For QlikView of corse

applymap function sounds ok.

Do you have an example for me?

sunny_talwar
Not applicable

Re: Excel Lookup SYNTAX - Script

Not applicable

Re: Excel Lookup SYNTAX - Script

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

Re: Excel Lookup SYNTAX - Script

Sorry for no confusion:


Mapping load * inline [

km, number

0, 1.5

51, 2

151, 3.75

];

Henric_Cronström
Not applicable

Re: Excel Lookup SYNTAX - Script

You should look at Intervalmatch. See IntervalMatch

HIC

sunny_talwar
Not applicable

Re: Excel Lookup SYNTAX - Script

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

Re: Excel Lookup SYNTAX - Script

Yea, thank you.

At the end it seems so clear an easy