topic Set variable wtih specific value in Water Cooler

Set variable wtih specific value

Tue, 15 May 2012 20:09:53 GMT

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

Set variable wtih specific value

swuehl — Tue, 15 May 2012 20:27:31 GMT

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?

Re: Set variable wtih specific value

rbecher — Tue, 15 May 2012 20:30:46 GMT

Hi Justin,

just use lookup():

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

- Ralf

Set variable wtih specific value

Tue, 15 May 2012 20:58:05 GMT

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

Set variable wtih specific value

rbecher — Tue, 15 May 2012 21:01:11 GMT

What do you mean with "any filters"?

Set variable wtih specific value

Tue, 15 May 2012 21:04:33 GMT

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.

Re: Set variable wtih specific value

rbecher — Tue, 15 May 2012 21:08:42 GMT

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

Set variable wtih specific value

Tue, 15 May 2012 21:17:14 GMT

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.

Re: Set variable wtih specific value

Tue, 15 May 2012 21:41:19 GMT

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