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

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

josephinetedesc — Thu, 19 Apr 2018 09:16:52 GMT

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)	1	2	4	Total
2014		1	4	2	7
2015		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

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

sasiparupudi1 — Thu, 19 Apr 2018 10:36:42 GMT

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;

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

josephinetedesc — Thu, 19 Apr 2018 10:58:10 GMT

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

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

sasiparupudi1 — Thu, 19 Apr 2018 11:01:31 GMT

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

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

josephinetedesc — Thu, 19 Apr 2018 11:10:12 GMT

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

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

sasiparupudi1 — Thu, 19 Apr 2018 11:20:00 GMT

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;

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

josephinetedesc — Thu, 19 Apr 2018 12:19:18 GMT

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:

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

sasiparupudi1 — Thu, 19 Apr 2018 12:20:45 GMT

can you pl attach your app and data?

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

josephinetedesc — Thu, 19 Apr 2018 12:21:57 GMT

this is what I would like to get to?

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

sasiparupudi1 — Thu, 19 Apr 2018 13:57:53 GMT

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

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

josephinetedesc — Thu, 19 Apr 2018 14:19:03 GMT

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

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

sasiparupudi1 — Fri, 20 Apr 2018 11:28:49 GMT

Did you manage to solve your issue?

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

josephinetedesc — Fri, 20 Apr 2018 13:38:35 GMT

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)???