Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
sasiparupudi1
Master III
Master III

Something like below?

19-04-2018 12-33-28.png

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

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
Master III
Master III

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

josephinetedesc
Creator III
Creator III
Author

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

Capture9.JPG

sasiparupudi1
Master III
Master III

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

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
Master III
Master III

can you pl attach your app and data?

josephinetedesc
Creator III
Creator III
Author

this is what I would like to get to?
Capture11.JPG

sasiparupudi1
Master III
Master III

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