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.
Quote from: CogUsrUSA22 on 01 Feb 2021 11:23:25 PM
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.
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 ?
Quote from: CogUsrUSA22 on 04 Feb 2021 01:20:45 PM
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.
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.
Quote from: CogUsrUSA22 on 04 Feb 2021 03:16:41 PM
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.