Hi All
sometimes it helps when I simplify my problem as shown below.
This is the table: 8 Artists, 4 Artists have 9 Appearance Dates and 4 Artists do not have an Appearance DAte
| RecordingDate | Artist | AppearanceDate |
| 15/09/2014 | John | 14/09/2014 |
| 18/09/2014 | Paul | 11/09/2014 |
| 9/07/2014 | Paul | 24/06/2014 |
| 31/07/2014 | Paul | 24/06/2014 |
| 25/12/2014 | George | 18/12/2014 |
| 12/11/2014 | George | 28/10/2014 |
| 27/11/2014 | George | 28/10/2014 |
| 9/03/2015 | Ringo | 2/03/2015 |
| 11/05/2015 | Ringo | 4/05/2015 |
| 3/12/2014 | Ringo | 18/11/2014 |
| 11/12/2014 | Ringo | 18/11/2014 |
| 31/07/2014 | Ringo | 24/07/2014 |
| 11/08/2014 | Mick |
| 4/08/2014 | Keith |
| 22/07/2014 | Bryan |
| 3/11/2014 | Ronnie |
I can get tthis table using Autonumber and the 3 duplicate dates are organised
| Artist | =if([AppearanceDate]='NULL','AppID 0',AppID) | AppearanceDate |
|---|
| | |
| Bryan | AppId1 | |
| George | AppId1 | 28/10/2014 |
| George | AppId2 | 18/12/2014 |
| John | AppId1 | 14/09/2014 |
| Keith | AppId1 | |
| Mick | AppId1 | |
| Paul | AppId1 | 24/06/2014 |
| Paul | AppId2 | 11/09/2014 |
| Ringo | AppId1 | 24/07/2014 |
| Ringo | AppId2 | 18/11/2014 |
| Ringo | AppId3 | 2/03/2015 |
| Ringo | AppId4 | 4/05/2015 |
| Ronnie | AppId1 | |
This table works well - (when I tick show all values)
| RecordingYear | Artist | AppID | AppId1 | AppId2 | AppId3 | AppId4 | - |
|---|
| 2014 | John | | 14/09/2014 | | | | - |
| Paul | | 24/06/2014 | 11/09/2014 | | | - |
| George | | 28/10/2014 | 18/12/2014 | | | - |
| Ringo | | 24/07/2014 | 18/11/2014 | | | - |
| Mick | | | | | | - |
| Keith | | | | | | - |
| Bryan | | | | | | - |
| Ronnie | | | | | | - |
| 2015 | Ringo | | | | 2/03/2015 | 4/05/2015 | - |
| - | - | | - | - | - | - | |
This table makes no sense - there is only 1 Artist with with 4 dates NOT 2 and none in 2015?
there were 4 Artists with no Appearance Dates - this table shows a 0
| RecordingYear | =aggr(count([AppearanceDate]),Artist) | 0 | 1 | 2 | 4 | Total |
|---|
| 2014 | | 0 | 1 | 4 | 2 | 7 |
| 2015 | | 0 | 0 | 0 | 2 | 2 |
What I want is to see all Artists with their corresponding number of Appearances AND I want to know hen an Artist had no appearances.
This is the script I used:
TheDates:
Load distinct
([Artist]& [AppearanceDate]) as AKey
,*
;
Load distinct
[Artist],
[AppearanceDate],
RecordingYear
;
LOAD distinct
[Artist],
[AppearanceDate],
yearname(RecordingDate) as RecordingYear
FROM commAsk.xlsx (ooxml, embedded labels, table is Sheet1);
Left Join (TheDates)
Load
RowNo() as RecNum,
AKey,
'AppId' & AutoNumber([Artist]&[AppearanceDate],[Artist]) as AppID
Resident TheDates
Order By Artist,[AppearanceDate]
;
Is there some way of getting a list of all those Artists with NO appearance Date - (Recording Year I can ignore ...)
Thank you
Jo
