Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
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

RecordingDateArtistAppearanceDate
15/09/2014John14/09/2014
18/09/2014Paul11/09/2014
9/07/2014Paul24/06/2014
31/07/2014Paul24/06/2014
25/12/2014George18/12/2014
12/11/2014George28/10/2014
27/11/2014George28/10/2014
9/03/2015Ringo2/03/2015
11/05/2015Ringo4/05/2015
3/12/2014Ringo18/11/2014
11/12/2014Ringo18/11/2014
31/07/2014Ringo24/07/2014
11/08/2014Mick
4/08/2014Keith
22/07/2014Bryan
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)

RecordingYearArtistAppIDAppId1AppId2AppId3AppId4-
2014John14/09/2014-
Paul24/06/201411/09/2014-
George28/10/201418/12/2014-
Ringo24/07/201418/11/2014-
Mick-
Keith-
Bryan-
Ronnie-
2015Ringo2/03/20154/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)0124Total
201401427
201500022

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

12 Replies
josephinetedesc
Creator III
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

Thank you for your help

Jo

Capture14.JPG

sasiparupudi1
Master III
Master III

Did you manage to solve your issue?

josephinetedesc
Creator III
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