topic Re: Excel Lookup SYNTAX - Script in QlikView

Excel Lookup SYNTAX - Script

Thu, 10 Sep 2015 09:38:39 GMT

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!

Michael

Re: Excel Lookup SYNTAX - Script

sunny_talwar — Thu, 10 Sep 2015 09:45:58 GMT

You are seeking help with Excel or QlikView?

Re: Excel Lookup SYNTAX - Script

vikasmahajan — Thu, 10 Sep 2015 09:47:36 GMT

you need to give applymap function in Qlikview.

Vikas

Re: Excel Lookup SYNTAX - Script

Thu, 10 Sep 2015 10:24:52 GMT

For QlikView of corse

applymap function sounds ok.

Do you have an example for me?

Re: Excel Lookup SYNTAX - Script

sunny_talwar — Thu, 10 Sep 2015 10:27:01 GMT

Check this blog out:

Mapping … and not the geographical kind

Re: Excel Lookup SYNTAX - Script

Thu, 10 Sep 2015 11:04:43 GMT

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:

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

Re: Excel Lookup SYNTAX - Script

Thu, 10 Sep 2015 11:07:29 GMT

Sorry for no confusion:

Mapping load * inline [

km, number

0, 1.5

51, 2

151, 3.75

];

Re: Excel Lookup SYNTAX - Script

hic — Thu, 10 Sep 2015 11:39:05 GMT

You should look at Intervalmatch. See IntervalMatch

HIC

Re: Excel Lookup SYNTAX - Script

sunny_talwar — Thu, 10 Sep 2015 11:41:15 GMT

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 [

100

190

134

];

Output:

Re: Excel Lookup SYNTAX - Script

Thu, 10 Sep 2015 11:52:04 GMT

Yea, thank you.

At the end it seems so clear an easy

Re: Excel Lookup SYNTAX - Script

sunny_talwar — Thu, 10 Sep 2015 11:55:01 GMT

Awesome

If you got what you wanted, I would suggest marking the correct answer and any useful answers to close this thread.

Best,

Sunny