Logic for repeating rows

[CogUsrUSA22]

Hello
I am trying to solve the following scenario
NAME   PROG_A  PROG_B
------------------------------
JOHN    Y              ''
JOHN     ''             Y
SCOTT    Y             
DAVE      ''            Y
DAVE      Y           ''

Desired Output

JOHN     Y           Y
SCOTT    Y           ''
DAVE      Y          Y

The original input is a result of few union queries if that information helps .
Basically I want to put repeating names into single row (currently I have two programs but it would be nice to know if there is a way if lets say there are x number of program columns )

Thank you so much for looking.

[MFGF]

Hello
I am trying to solve the following scenario
NAME   PROG_A  PROG_B
------------------------------
JOHN    Y              ''
JOHN     ''             Y
SCOTT    Y             
DAVE      ''            Y
DAVE      Y           ''

Desired Output

JOHN     Y           Y
SCOTT    Y           ''
DAVE      Y          Y

The original input is a result of few union queries if that information helps .
Basically I want to put repeating names into single row (currently I have two programs but it would be nice to know if there is a way if lets say there are x number of program columns )

Thank you so much for looking.

Hi,

Try setting the aggregation type property for PROG_A and PROG_B to Maximum - see if this gives you what you need.

Cheers!

MF.

[CogUsrUSA22]

Thank you MF , but it didn't work , I made it work by using 1's and 0's for 'Y' and 'N' by simply aggregating it and  using the summary values , but it is not a clean way to do it as I wanted to list it as shown below (as list items vs summary items) so that I can perform other modifications to the list (as per my need)

Is there any other way you can think of ?

[MFGF]

Thank you MF , but it didn't work , I made it work by using 1's and 0's for 'Y' and 'N' by simply aggregating it and  using the summary values , but it is not a clean way to do it as I wanted to list it as shown below (as list items vs summary items) so that I can perform other modifications to the list (as per my need)

Is there any other way you can think of ?

Hi,

Did you set both the Detail Aggregation and Summary Aggregation properties to Maximum for both items? Not sure why it wouldn't work?

I just mocked this up locally and it works fine for me?

Cheers!

MF.

[CogUsrUSA22]

You are right . I missed one of the aggregation types.
This helps a lot . Thanks again .

I have another scenario (that I simplified in the example below and did not include in my previous question as it would complicate )

  Name    City           DATE_VISITED  AUSTIN_CONTACT  LONDON_CONTACT         
   MFGF   AUSTIN      4 FEB 2021         JOHN       
   MFGF   AUSTIN      2 FEB 2008         ROSS
   MFGF   LONDON     1 JAN 2021                                         JACK

DESIRED OUTPUT

  NAME       DATE_VISITED  AUSTIN_CONTACT    LONDON_CONTACT
  MFGF       4 FEB 2021        JOHN                        JACK

     So things to note in the output is that - The City column is gone  and I  need all records with common name in one row (kind of vertical pivot) but when i see a city repeating , I take the latest DATE_VISITED  record  for CONTACT column and when the city is different , I put the respective contact in the CITY_CONTACT column . (Please note there are only limited known cities in my scenario  and I am trying to do this by if else / case statements ) . So far I have not been successful.                             

[MFGF]

You are right . I missed one of the aggregation types.
This helps a lot . Thanks again .

I have another scenario (that I simplified in the example below and did not include in my previous question as it would complicate )

  Name    City           DATE_VISITED  AUSTIN_CONTACT  LONDON_CONTACT         
   MFGF   AUSTIN      4 FEB 2021         JOHN       
   MFGF   AUSTIN      2 FEB 2008         ROSS
   MFGF   LONDON     1 JAN 2021                                         JACK

DESIRED OUTPUT

  NAME       DATE_VISITED  AUSTIN_CONTACT    LONDON_CONTACT
  MFGF       4 FEB 2021        JOHN                        JACK

     So things to note in the output is that - The City column is gone  and I  need all records with common name in one row (kind of vertical pivot) but when i see a city repeating , I take the latest DATE_VISITED  record  for CONTACT column and when the city is different , I put the respective contact in the CITY_CONTACT column . (Please note there are only limited known cities in my scenario  and I am trying to do this by if else / case statements ) . So far I have not been successful.                           

Hi,

That complicates things a little, but the same principle should still apply. The trick here is to identify the row for each name/city combination that corresponds to the latest date_visited for that combination. You could first use a query calculation (let's call it [LATEST CITY]) following this approach

if ([DATE_VISITED] = maximum([DATE_VISITED] for [NAME], [CITY])) then ([CITY]) else (null)

You could then add a detail filter to the report (after aggregation) to remove any other rows:

[CITY] = [LATEST CITY]

This should bring the result set down to two rows with JOHN and JACK on them. You can then use the approach we tried earlier?

I may be missing something if the scenario is more complex, but this is probably the way I'd go.

Cheers!

MF.