How do I deal with Autonumber() and Dates = null?

[josephinetedesc]

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

[sasiparupudi1]

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;

[josephinetedesc]

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

[sasiparupudi1]

Please look at the picture and try my script above and see if it helps?

[josephinetedesc]

I applied the script - but this is what I get:

[sasiparupudi1]

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;

[josephinetedesc]

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:

[sasiparupudi1]

can you pl attach your app and data?

[josephinetedesc]

this is what I would like to get to?

[sasiparupudi1]

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