Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I have figured out what i need to do in Excel - so the table needs that translation ...
ie count the dates in the columns or count the nulls
so what I need to do is to count the dates or the nulls in the columns ....
It is now tomorrow but at least I will sleep
Thank you for your help
Jo
Did you manage to solve your issue?
Sort of …
At least I know now what to do in Excel!
Basically the answer is correct but needs to be interpreted (so maybe change the column headings … )
Thank you for your help. Was the explanation clear about how I had to do the if(cell is a date, 0,1)???
Jo