topic Re: VLookup in QlikView

VLookup

Thu, 24 Mar 2016 14:08:28 GMT

Hey,

Does anyone know how to convert this Excel V-Lookup into a QV formula?

=TODAY()-(VLOOKUP(VLOOKUP(J2,A:L,10),A:L,11))

Thanks!!

Re: VLookup

robert_mika — Thu, 24 Mar 2016 14:09:43 GMT

Search forum for Applymap

Don't join - use Applymap instead

Re: VLookup

sunny_talwar — Thu, 24 Mar 2016 14:10:09 GMT

I would do something like this:

Today() - ApplyMap('MappingTableName', FieldName) as Something

Re: VLookup

Thu, 24 Mar 2016 14:13:24 GMT

Try with ApplyMap function.

Re: VLookup

Thu, 24 Mar 2016 14:47:51 GMT

Hi there,

I think this may not be the right function for me.

What I am trying to do is:

IF task 1 is dependent on task 2, then check if task 2 is overdue. If it is, then make RAG status red.

If task 2 is dependent on task 3, and task 3 is overdue, then both task 2 and task 1 should become RED (as they're all dependent on one another)

Task	Due Date	Dependent on
Task 1	01/01/2016	Task 2
Task 2	01/03/2016	Task 3

Re: VLookup

Tue, 29 Mar 2016 09:11:42 GMT

Hi there,

I'm struggling with this a little bit. Is it possible to show me an example of what this would look like using ApplyMap

=IF(Risk!A3="","",TODAY()-VLOOKUP(Risk!J3,Risk!A:L,11))

I am guessing that it would be using field names rather than Excel columns, so I've included that below:

=IF(Ref="","",TODAY()-VLOOKUP(Dependent,Ref,TargetDate))

Any help would be greatly appreciated

Re: VLookup

amayuresh — Tue, 29 Mar 2016 09:20:27 GMT

1. if you have two columns in source table and want to take value from second column then use ApplyMap() function.

2. if you have more than two column in source table and want to take value from any column from 2nd column then use lookup()

Second option is same vlookup() formula in excel, only parameter sequence is different.

Re: VLookup

robert_mika — Tue, 29 Mar 2016 09:24:39 GMT

Have you looked at the link provided?

See below

The first table (Ref) is your lookup table where T1 is your data for the lookup table

The second table (Orders or whatever you will call this table)is your table where you want to return your data

Ref:
Mapping Load Dependent, TargetDate From T1 ;

The second step is to use this information when reading the order table, such as:

Orders:
Load *,

Today()- ApplyMap('Ref', Dependent, null()) as Something
From T2 ;

Re: VLookup

Tue, 29 Mar 2016 09:26:06 GMT

Oh really, I can use the same vlookup?

What is the parameter diff?

Re: VLookup

amayuresh — Tue, 29 Mar 2016 09:35:14 GMT

Please refer this link for more details: LOAD data into QlikView

Re: VLookup

amayuresh — Tue, 29 Mar 2016 09:45:13 GMT

Examples:

Lookup:

ApplyMap():

Re: VLookup

Tue, 29 Mar 2016 09:49:44 GMT

Thanks, I have a basic ApplyMap working. How would I do a nested ApplyMap?

Here is the working one

Today()- ApplyMap('Ref', Dependent, null()) as Dependency2

Here is the nested VLookup I'm trying to mirror

=IF(Ref="","",TODAY()-(VLOOKUP(VLOOKUP(Dependent,Risk!A:L,Dependent),Ref,TargetDate)))

I attempted with an nested ApplyMap but appear to have broken it

Re: VLookup

amayuresh — Tue, 29 Mar 2016 10:04:34 GMT

Please post sample data of both tables

Re: VLookup

robert_mika — Tue, 29 Mar 2016 10:06:10 GMT

What is the parameter diff?

Could you elaborate?

Feeling Qlikngry?

How To /Missing Manual(25 articles)

Re: VLookup

robert_mika — Tue, 29 Mar 2016 10:06:53 GMT

Post your formula.

Re: VLookup

Tue, 29 Mar 2016 10:14:07 GMT

Hi,

Here is the nested formula I am trying, It has syntax errors, any ideas?

Today()- ApplyMap(ApplyMap('Ref', Dependent, null()), 'Ref', Dependent, null()) as Dependency1,

Re: VLookup

Tue, 29 Mar 2016 10:16:24 GMT

I also tried this one:

Today()- ApplyMap('Ref',ApplyMap('Ref', Dependent, null()), Dependent, null()) as Dependency1,

Re: VLookup

amayuresh — Tue, 29 Mar 2016 10:20:21 GMT

Please check below is my example:

Script:

Output:

Re: VLookup

Anonymous — Tue, 29 Mar 2016 10:23:29 GMT

To create the temp table and resident the to the particular table it may be get the values.

Re: VLookup

amayuresh — Tue, 29 Mar 2016 10:27:59 GMT

As per your formula mentioned above (VLOOKUP(Dependent,Risk!A:L,Dependent)), you have more than two columns from source table so use lookup() function to get the target column value.