Set variable wtih specific value

[#1]

Hi,

This might be much simpler than i'm making it out to be, but is there a way to set a variable to a value based on another field value?  For example, I have a Week Number variable set to 5, hard-coded, and I want to set a Week End Date variable from the table where Week Number = 5.  Basically look up this value in a table according to this other value and store the result in a variable.  There would always be one value returned from this lookup.  Any ideas?

Thanks

Justin

[swuehl]

I like to say: potentially yes, but maybe I misunderstood your requirement.

Ok, you have a week number variable set to 5, but how does you other table look like (what's the structure)?

Could you give a short example together with the desired outcome?

[rbecher]

Hi Justin,

just use lookup():

let vWeekEndDate = lookup('WeekEndDateField', 'WeekNumberField', $(vWeekNumber));

- Ralf

[#4]

Would this function respect any filters that have been added?  For example, my WeekNumberField may be 5, but there may also be a filter on year 2012.  So in that case I'd want the WeekEndDate to be 2/2/2012, not 2/3/2011.  Ideally I'd want something similar to set analysis, where i can define a number of filters and it would return the value for a field where those conditions are met.  Or in sql the selection of a field with a multi-part where clause.  I think lookup() is close though...

[rbecher]

What do you mean with "any filters"?

[#6]

Meaning if there is a filter selection on Year, would the lookup respect that?  In the above example, there would be two valid Week_End_Dates for Week_Number 5:  2/3/2012 and 2/2/2011 (one for 2012 and one for 2011).  But if they have a filter on 2012 only, i would want 2/3/2012 to be returned.

[rbecher]

Don't understand your approach at all. Maybe you can use set analysis. Inter record functions like lookup() will only work in script...

[#8]

OK, let me try once more a different way, since I realize I am probably not making sense.  Consider this table:

Basically I want to retrieve that value in bold, the WTD amount where the date is the last day of the week (according to the Week_End_Date).  I am using set analysis like this:

sum({$<Week_Of_Year={$(vCurrentWeek)}, Year = {$(vCurrentYear)} >} wtd_amount)

But what I'm missing is something to say Date = "1/26/2012", or Date = Week_End_Date.  Hope that helps, and thanks for your assistance.

[#9]

I think I got it:

=sum({$<Date = P({1<Week_Of_Year={$(vCurrentWeek)}, Year = {$(vCurrentYear)}>} Week_End_Date ) > } wtd_net_revenue_amt)

So it returns all the possible Week_End_Date's for the specificed Week_Of_Year and Year (which is only one, 1/26/2012), and sets the Date equal to that value.  Thanks...