Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for
Did you mean:
Creator III

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

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
BryanAppId1
GeorgeAppId128/10/2014
GeorgeAppId218/12/2014
JohnAppId114/09/2014
KeithAppId1
MickAppId1
PaulAppId124/06/2014
PaulAppId211/09/2014
RingoAppId124/07/2014
RingoAppId218/11/2014
RingoAppId32/03/2015
RingoAppId44/05/2015
RonnieAppId1

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:

([Artist]& [AppearanceDate])  as AKey

,*

;

[Artist],

[AppearanceDate],

RecordingYear

;

[Artist],

[AppearanceDate],

yearname(RecordingDate) as RecordingYear

FROM commAsk.xlsx (ooxml, embedded labels, table is Sheet1);

Left Join (TheDates)

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

12 Replies
Creator III
Author

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

Jo

Master III

Did you manage to solve your issue?

Creator III
Author

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

Community Browser