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

VLookup

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!!

1 Solution

Accepted Solutions
amayuresh
Creator III
Creator III

Examples:

Lookup:

1.PNG

ApplyMap():

1.PNG

2.PNG

View solution in original post

19 Replies
robert_mika
Master III
Master III

Search forum for Applymap

Don't join - use Applymap instead

sunny_talwar

I would do something like this:

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

Not applicable
Author

Try with ApplyMap function.

Not applicable
Author

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)

  

TaskDue DateDependent on
Task 101/01/2016Task 2
Task 201/03/2016Task 3
Not applicable
Author

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

amayuresh
Creator III
Creator III

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.

robert_mika
Master III
Master III

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 ;

Not applicable
Author

Oh really, I can use the same vlookup?

What is the parameter diff?

amayuresh
Creator III
Creator III

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