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
Something like below?
T1:
Load
Date(Date#(RecordingDate,'DD/MM/YYYY'),'DD/MM/YYYY') as RecordingDate,
Artist,
Date(Date#(AppearanceDate,'DD/MM/YYYY'),'DD/MM/YYYY') as AppearanceDate,
AutoNumber(Artist,'Artist') As ArtistNo
Inline
[
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,
25/12/2014,George,18/12/2014
12/11/2014,George,28/10/2014
27/11/2014,George,
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
];
Final:
Noconcatenate Load
RecordingDate,Artist,AppearanceDate,
Year(RecordingDate) as RecordingYear,
If((Artist=Peek(Artist) AND Len(Trim(AppearanceDate))=0),Peek(AppearanceDate),AppearanceDate) as AppearanceDateNew,
If((Artist=Peek(Artist) AND Len(Trim(AppearanceDate))=0),1,0) As IsDateMissing
Resident T1
//Where Artist='Paul'
Order By
Artist ,ArtistNo desc
;
Drop Table T1;
Thanks Sasidhar
I cannot see qvw files as I don't have a home licence,
But Artists with any missing Appearance dates should be: Mick, Keith, Bryan and Ronnie
Jo
Please look at the picture and try my script above and see if it helps?
I applied the script - but this is what I get:
The Flag IsDateMissing is used to identify artists where a date has been populated ex(Paul and George). I have added a new flag to identify null dates.. pl check again
Try
T1:
Load
Date(Date#(RecordingDate,'DD/MM/YYYY'),'DD/MM/YYYY') as RecordingDate,
Artist,
Date(Date#(AppearanceDate,'DD/MM/YYYY'),'DD/MM/YYYY') as AppearanceDate,
AutoNumber(Artist,'Artist') As ArtistNo
Inline
[
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,
25/12/2014,George,18/12/2014
12/11/2014,George,28/10/2014
27/11/2014,George,
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
];
Final:
Noconcatenate Load
RecordingDate,Artist,AppearanceDate,
Year(RecordingDate) as RecordingYear,
If((Artist=Peek(Artist) AND Len(Trim(AppearanceDate))=0),Peek(AppearanceDate),AppearanceDate) as AppearanceDateNew,
If((Artist=Peek(Artist) AND Len(Trim(AppearanceDate))=0),1,0) As IsDateMissing
Resident T1
//Where Artist='Paul'
Order By
Artist ,ArtistNo desc
;
Drop Table T1;
T3:
NoConcatenate Load
*,
If(Isnull(AppearanceDateNew),1,0) AS IsAppearanceMissing
Resident Final;
;
Drop Table Final;
Sorry but I cannot add the files when using the interface???There are
2 tabs (for the 2 different scripts) and 2 sheets responding to the 2
different scripts)
There is also the spreadsheet - which I notice does not give the
Distinct dates per Artist - but all dates..
Jo:
can you pl attach your app and data?
this is what I would like to get to?
Try may be like this
Pivot Table
Dimensions: Record Year,Artist
Expressions
1)Appearnce Dates :Concat(AppearanceDate,',')
2) Total:Count(TOTAL<RecordingYear, Artist>AppearanceDate)
hth