Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
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