Distinct first name and last name

[rwaugh]

Hello Everyone,

I am hoping one of you will be able to help me with my current issue.  I have two queries that I join to obtain my final data set.  Query 1 contains a listing of clients First and Last Names, along with a unique identifier.  Query 2 contains the same unique identifier but also contains alternate first names and alternate last names.  As a result, the JOIN query could contain more than one record for each unique identifier, which is fine.  The issue is that I would like to then take that data and output it so that there is only one record for each unique identifier.  The catch is that I would like the First and Last Name columns to contain UNIQUE names separated by "/".  For example, if John Doe (ID 123456) has several alternate names I only want the unique names to show - ID 123456 has the following First and Last Alternate names:  John Does, John Doesnt, Joe Does, Joe Doesnt, Joe Wants.  So the final output should be the First and Last name from Query 1 followed by the other known names:  John/Joe (for the first name) and Doe/Does/Doesnt/Wants (for the last name).

Any thoughts/ideas?

I tried using repeaters but the repeaters, although working, does not allow me to pull in just the unique names (it would use John/John/Joe/Joe/Joe for example).

Thanks!

[cognostechie]

A unique identifier is a value which identifies a record uniquely and that means no other record will have that value. From what you said, your Query 2 contains the identifier but it is not a unique identifier.

Try using 'max' function for the identifier to get one name at a time in one column. Use a series of columns using max function which will give you the max of remaining value. Just an idea.. maybe somebody will come up with a better ides.

I usually do these things in the code (SQL) by using row_number () over (partition by order by ) function.

[rwaugh]

Well, I finally got the results I was looking for.  As usual, shortly after posting this I had a brain fart and tried a few things that I hadn't thought of before.  My new issue is now ensuring that the "repeater" columns are exported into excel as one column each instead of multiple columns (Not a huge deal as I can simple delete the extra columns that are blank, but it just looks ugly initially).

Thanks for the reply cognostechie! 

[Cognos_Jan2017]

My new issue is now ensuring that the "repeater" columns are exported into excel as one column each instead of multiple columns (Not a huge deal as I can simple delete the extra columns that are blank, but it just looks ugly initially).

For the "extra" repeater columns, try leaving them in your List Design, but select the "List Column" property of any column(s)
NOT to be exported into Excel, and set its(their) Render to No.

HTH, Bob

[rwaugh]

For the "extra" repeater columns, try leaving them in your List Design, but select the "List Column" property of any column(s)
NOT to be exported into Excel, and set its(their) Render to No.

HTH, Bob

Thanks Bob!  The issue is that the columns that are showing in Excel aren't actually showing in the list design.  In my design I have just the one column for First Name and one for Last Name.  When it comes out in Excel the heading for the column is 3 columns (merged) but the actual data is only in the first column.  The other 2 columns are blank.  All the research I have done up to this point is telling me that there is no fix for this...I'm hoping everyone is wrong though lol

[Cognos_Jan2017]

Can you drag in a Repeater, and assign concatenation (First/ Last Names, and forward slash) something like ...
[FirstName]+' / ' + [LastName]+' / '

Your "actual data is only in the first column." could be your Repeater, knowing the "other 2 columns" are blank.
What if you DON'T merge those 3 columns, but just Render the "actual data column"?

[rwaugh]

My apologies for the long delay in responding.  The issue I am having is regarding the output of the report.  For now I am just deleting the unwanted blank columns manually but it would be ideal if this were not the case.  I've attached a screenshot of the report in Cognos.  The Excel output is fine, except that the heading for First Name is in columns B, C and D.  For Last Name the column heading is in columns E, F and G.  The data for first name is in column B only and the data for last name is in column E only.  C, D, F and G are complete blank (except for the column heading).

Thanks,

[Cognos_Jan2017]

The "unwanted blank columns" ... unlock the List, select each "unwanted blank column"s List Column and Render it as No.  That way, those columns are still in the List, but won't render when Run as Excel.

Let us know.  TIA, Bob