11 Replies Latest reply: Jan 4, 2018 4:18 PM by Schyler Ruhland RSS

    How do you create a field (& populate it) using a field from a crosstable?

    Schyler Ruhland

      I apologize for the vague header...


      I am trying to create a 'Unique Key' table which will be made up of 2 fields. The two fields I am joining are [Negotiator] and [Full Date] (full date is Month and Year spanning May 2015 to December 2017). In short, I want to create a table with every permutation of name and date. I need the unique key because the relationship between name and date becomes many-to-many as I pull in more data points in - example of what I need:


          ' John May 2015

           John June 2015...

           ... John December 2017


           Mike May 2015

           Mike June 2015...

          ... Mike December 2017


           Jane May 2015

           Jane June 2015...

          .... Jane December 2017 '



      I tried keeping it simple and just combine the two, something like: Negotiator & [Full Date] as [Unique Key]

      ... but quickly realized QV cannot recognize the field [Negotiator] as a relatable field; something to do with it being created in the crosstable.


      Below is an outline of my query:


      CrossTable([Negotiator], Data_QA)
      LOAD Date as [Full Date],
      [John Doe],
      [Mike Doe],
      [Jane Doe]

      - (there are about 40 names) -



      Hope this is enough information to understand the issue. Any guidance / suggestions on how to create this joined unique key field?