4 Replies Latest reply: Aug 25, 2017 12:57 PM by Brian Head RSS

    Using CONCAT and MATCH functions together

    Brian Head

      Hi,

       

      Still relatively new to QV. I have a table that has a variable with multiple values. Each instance creates a new row in a joined table. I don't what this. Instead I want a single variable with all strings. I have code that worked for this when I wasn't limiting to certain criteria, but when I try to do that I have problems.

       

      Successful code:

       

      Left Join(Donor)

      LOAD

      "Donor ID",

      Concat(Distinct("DP Flag"),', ') as [Concatenated DP Flags]

      FROM [lib://Warehouse - example table a.QVD]

      (qvd)

      Group By [Donor ID];

       

       

      *******************************************************************************

      Unsuccessful code when trying to limit to a new variable "GIVEMETH" when the  "MV Field Name" = "GIVEMETH". The resulting variable GIVEMETH could contain multiple string flags.

       

      Left Join(Donor)

      LOAD

           "MV Matching ID" as [Donor ID],

          "MV Field Name" as [Field Name],

          Code,

      match([Field Name],'GIVEMETH'),(Concat(Distinct(Code),', ')) as [GIVEMETH]

      FROM [lib://Warehouse - example table b.QVD]

      (qvd)

      Group By [Donor ID];

       

      With the above code I've gone through multiple iterations with error messages ranging from "Code" not found to other things. Any suggestions would be helpful.

        • Re: Using CONCAT and MATCH functions together
          Sunny Talwar

          Try this

           

          Left Join(Donor)

          LOAD

              "MV Matching ID" as [Donor ID],

              Concat(Distinct If(Match("MV Field Name", 'GIVEMETH'), Code), ', ') as [GIVEMETH]

          FROM [lib://Warehouse - example table b.QVD] (qvd)

          Group By "MV Matching ID";

           

          or this

           

          Left Join(Donor)

          LOAD

          "Donor ID",

          Concat(Distinct("DP Flag"),', ') as [Concatenated DP Flags]

          FROM [lib://Warehouse - example table a.QVD]

          (qvd)

          Where Match("MV Field Name", 'GIVEMETH')

          Group By [Donor ID];

            • Re: Using CONCAT and MATCH functions together
              Brian Head

              Thanks, Sunny. The first suggestion results in an invalid expression error. The second suggestion doesn't use the variables I need. I tried modifying it to include the variables needed as below, but I still get an invalid expression error.

               

               

              Left Join(Donor)

              LOAD

              "MV Matching ID" as [Donor ID],

                  "MV Field Name" as [Field Name],

                  Code,

                  concat(DISTINCT(Code),', ') as [concatenated givemeth]

              FROM [lib://Warehouse - DonorPerfect/DonorPerfect.DPUserMultiValues.QVD]

              (qvd)

              Where Match("MV Field Name",'GIVMETH')

              Group By "MV Matching ID";

               

               

              Any other thoughts?

                • Re: Using CONCAT and MATCH functions together
                  Sunny Talwar

                  Because you need to use all your non-aggregating fields in your group by statement....

                   

                  Left Join(Donor)

                  LOAD

                  "MV Matching ID" as [Donor ID],

                      "MV Field Name" as [Field Name],

                      Code,

                      concat(DISTINCT(Code),', ') as [concatenated givemeth]

                  FROM [lib://Warehouse - DonorPerfect/DonorPerfect.DPUserMultiValues.QVD]

                  (qvd)

                  Where Match("MV Field Name",'GIVMETH')

                  Group By "MV Matching ID", "MV Field Name", Code;

                   

                  But what is the benefit of adding Code when you are concatenating it? I think you might want to remove it from your load

                   

                  Left Join(Donor)

                  LOAD

                  "MV Matching ID" as [Donor ID],

                      "MV Field Name" as [Field Name],

                      Code,

                      concat(DISTINCT(Code),', ') as [concatenated givemeth]

                  FROM [lib://Warehouse - DonorPerfect/DonorPerfect.DPUserMultiValues.QVD]

                  (qvd)

                  Where Match("MV Field Name",'GIVMETH')

                  Group By "MV Matching ID", "MV Field Name", Code;