4 Replies Latest reply on Jul 1, 2016 3:59 AM by ahmed.hasnaoui

    conditional constants based on other column

    ahmed.hasnaoui

      Hi all, I have a data set of workers with their time sheets with hours etc. Now I have another data source with two columns with the working days per month:

        

      MonthWorkdays
      Januar 1620
      Februar 1620
      März 1621
      April 1621
      Mai 1619
      Juni 1622
      Juli 1621
      August 1622
      September 1622
      Oktober 1621
      November 1622
      Dezember 1619

       

      Now every months beginning I get a new data source with the months time values.

      How can I do a conditional lookup to the data source above that I can select the workdays that I need to do some further calculation based on that value (e.g. next month would be july 16 and the value would be 21)

       

      Thank you in advance!

        • 1. Re: conditional constants based on other column
          Ivan Monnier

          Hello,

           

          you can use blending.

           

          Create a data source with your reference working days. It can be an excel file, or even a copy/paste. Be sure to enter the dates as dates, not month name.

           

          Then connect to your datasource (I used the superstore), and change the relationships to ensure that the date of your datasource will be related to the month by the same month.

           

          After this, you can build your view with both datasource (ensure that the link symbol is activated).

           

          Hope it helps

           

          Ivan

          1 of 1 people found this helpful
          • 2. Re: conditional constants based on other column
            ahmed.hasnaoui

            Hi Ivan, thank you for your help, your solution is correct. However, I need to add another time sheet of the next month. How can I know use two different blendings resp. relationships? I managed to do this but only successful when I start with a clear worksheet... Isn't it possible to duplicate worksheet and change the relations afterwards? I wasn't able to do this :( If I use the Replace Data Source function all the relations in the workbook are changed...

            • 3. Re: conditional constants based on other column
              Ivan Monnier

              Ahmed,

               

              unfortunately, I do not know how to change the main and secondary datasources without starting the worksheet from scratch.

               

              An idea though: is is possible for you to "add" the new timesheet to the former one?

              Either you have a mean to add new lines in your datasource,

              or, if you use text files, you can "union" the timesheets,

              or, when using an extract, you can update it with a new datasource (screencopy in french).

               

               

              The goal is to keep the same datasource in order to avoid rebuilding your worksheet every month.

               

              I hope that I am clear.

               

              Ivan

              1 of 1 people found this helpful
              • 4. Re: conditional constants based on other column
                ahmed.hasnaoui

                Hi Ivan, thank you I think the only solution is to add the data to the former one, we can do this. Thank you!