Using CONCAT and MATCH functions together

[#1]

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.

[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];

[#3]

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?

[sunny_talwar · Accepted solution]

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;

[#5]

Thank you very much, stalwar1 That did exactly what I needed.