5 Replies Latest reply: Sep 5, 2013 7:33 AM by Phil Speight RSS

    Create a link Table

    Phil Speight

      Can anyone show me how I can show which roster cycle a person is in based on the the the division they work in.  Each staff member works within a division.  Each division has it's own cycle.  Each Cycle has it's own publish date (based on the date of hours owed), I keep getting duplications :

       

      Hours owed:

       

      Date,

      Hours owed,

      Staff Number

       

      Staff:

       

      Title,

      First Name,

      Last Name,

      Staff Number,

       

      Division:

       

      Division Name,

      Staff Number

       

      Cycle Name:

       

      Cycle Name

      Division Name

       

      n.b.  each division will either be cycle 1 or cycle 2

       

      Cycle Dates:

       

      Hours Owed Date

      Cycle Name

      Publish Date

       

      n.b.  There is a duplication in this table as there are 2 hours owed dates, one of them has a publish date for cycle 1 and a the other has a publish date for cycle 2.

       

      I need a way to show the publish date (without duplications) for the staff members hours owed from a specific hours owed date.  Any Help Appreciated.

       

       

      Thanks

       

       

       

      Phil

        • Re: Create a link Table
          Kaushik Solanki

          Hi,

           

               I see a straight linking between them, why would you require the link table?

           

               Why dont you load them as it is? what is the problem after doing this, if you have already done this?

           

          Regards,

          Kaushik Solanki

          • Re: Create a link Table
            Phil Speight

            Hi

             

            I end up with 2 rows for each staff member.  Please see example

             

            Hours Owed DateDivisionPerson_StaffNumberPerson_FirstNamePerson_SurnamePBalance_CodeNameRoster CyclePublish DatePublishedRoster Cycle1Hours Owed Value
            -
            19/08/2013B811111111First NameLast NameHrs +/- TargetStott01/07/2013PublishedEccles5.5
            19/08/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott5.5
            26/08/2013B811111111First NameLast NameHrs +/- TargetStott01/07/2013PublishedEccles-8
            26/08/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott-8
            02/09/2013B811111111First NameLast NameHrs +/- TargetStott01/07/2013PublishedEccles-9.5
            02/09/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott-9.5
            09/09/2013B811111111First NameLast NameHrs +/- TargetStott01/07/2013PublishedEccles-11
            09/09/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott-11
            16/09/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott-12.5
            16/09/2013B811111111First NameLast NameHrs +/- TargetStott26/08/2013PublishedEccles-12.5
            23/09/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott-14
            23/09/2013B811111111First NameLast NameHrs +/- TargetStott26/08/2013PublishedEccles-14
            30/09/2013B811111111First NameLast NameHrs +/- TargetStott22/07/2013PublishedStott-15.5
            30/09/2013B811111111First NameLast NameHrs +/- TargetStott26/08/2013PublishedEccles-15.5
            07/10/2013B811111111First NameLast NameHrs +/- TargetStott26/08/2013PublishedEccles-53
            07/10/2013B811111111First NameLast NameHrs +/- TargetStott16/09/2013Not Yet PublishedStott-53
            14/10/2013B811111111First NameLast NameHrs +/- TargetStott26/08/2013PublishedEccles-54.5
            14/10/2013B811111111First NameLast NameHrs +/- TargetStott16/09/2013Not Yet PublishedStott-54.5


            • Re: Create a link Table
              Kaushik Solanki

              Hi,

               

              Can you upload the example qvw file, it would help to understand your problem in detail.

               

              Regards,

              Kaushik Solanki

              • Re: Create a link Table
                Phil Speight

                Hi

                 

                I can't as there is too much data that needs anonomising.

                 

                I know the problem is a simple one to resolve. I am just too much of a novice. I need a way that the script filters any cycle publish date information if the person does not belong to that roster cycle.

                 

                The problem I have is that the person appears on 2 rows (once from cycle A & once for cycle B).

                 

                I know I am making an easy query very complicated.

                 

                 

                Phil