Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

vlookup style calculation required

Hi,

am trying to get a specfic value to appear in a text object.  My data is as follows

Date,  Working Day, Total Work Days

2/7/12,     1,     22

3/7/12,     2,     22

4/7/12,     3,     22

5/7/12,     4...

and so it continues to the end of the month giving the working day.

I don't have a problem displaying the total working days i.e. 22; however how do I display today's working day?  (If I was using Excel it would simply be =(VLOOKUP(today(),A1:Cxx,2.FALSE).)  Is it possible to do something similar in my Qlikview doc?

Thanks in advance.

C

9 Replies
Not applicable

Re: vlookup style calculation required

Dear C,

Try using the following syntax:

FieldValue('Working_Day',FieldIndex('Date',Today()))

Note the change in field name, it seems you would have to change the mane to make it work.

Regards

Rahul

 

MVP
MVP

Re: vlookup style calculation required

I think you could also use

FieldValue('[Working Day]', ...

There is also a Lookup function

Lookup('[Working Day]', 'Date', Today(), 'DateTable')

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: vlookup style calculation required

However i believe  lookup is a script level function

Regards

Rahul Lakhina

Not applicable

Re: vlookup style calculation required

Hi Rahul,

Thank you for that.  Within the parameters of my simple example that seemed to work fine; however whe I tried to apply it to the actual document I'm working on it fails.  It simply return a "-". I've tried various varibles and variations to no avail.  I attach a sample of what I have with your suggestion, could I ask you to point out what it is I'm doing wrong?

Thank you for your advice in advance.

C

MVP
MVP

Re: vlookup style calculation required

Hi

The fieldindex/fieldvalue aproach only works if you use the same field (or in some cases fields that are loaded simultaneously with the same cardinality and a 1:1 correspondence). Your workday and calendardate fields do not meet these requirements.

However, the two fields are associated, and you can see that working in your table box. For a straight table, if CalendarDate is one of the dimensions, just use =WorkDay. The associations will take care of the lookup for you.

I hope that I understood your requirements correctly. See the attached for a straight table chart as I described.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: vlookup style calculation required

Hi Jonathan,

Thank you for your response and I see what you mean with regards to the one-to-one relationship.

My requirement is simply to have what the working day is 'today' so that I can then use that value in a calculation for average daily sales for the current month to date. i.e. I've made £1m from the 1/7/2012 to-date so that would be £1m/6 working days. I don't actually want the user to be selecting todays date.  Does that make sense?

MVP
MVP

Re: vlookup style calculation required

Perhaps something like

=only({<Date = {"$(=Today())"}>} WorkingDay)

will return today's working day number. Check that it works, and you can then use that expression as the divisor for your average.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: vlookup style calculation required

Jonathan,

Thank you for the suggestion however it didn't appear to provide me a result.

I have actually found a work around for the purpose of my document which relates back to your suggestion of using lookup.  I am declaring a variable at the script level - LET varWorkDay = Lookup('WorkDay', 'Cal_Date', today(), 'DateTable').

Thanks for the advice.

C

Not applicable

Re: vlookup style calculation required

= only(${< Date = {Today() } >} [Working Day] )

Community Browser