8 Replies Latest reply: May 15, 2012 5:41 PM by Justin Martin RSS

Set variable wtih specific value

Justin Martin

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

    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
    Ralf Becher

    Hi Justin,

     

    just use lookup():

     

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

     

    - Ralf

    • Set variable wtih specific value
      Justin Martin

      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
        Ralf Becher

        What do you mean with "any filters"?

        • Set variable wtih specific value
          Justin Martin

          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
            Ralf Becher

            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
              Justin Martin

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

               

              5-15-2012 5-13-13 PM.png

              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
                Justin Martin

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