Need a total count of series of dates (Count IF column has date)

[bundleminion]

Hello everyone,

I've been lurking on the site and have found a lot of good information and guidance here so first of all I would love to thank all of you for what you do here.

Now the issue I am up against. I'm still very much a newb in Qlik and haven't found this question posted (though that could be due to not being sure how to pose the question). I have a series of columns/fields of service dates (ex: servicedate1, servicedate2, and so on..) in a data set and am needing to create a field that has a count of the the service date fields that contain a date.

Example:

ServiceDate1       ServiceDate2      ServiceDate3       ServiceDateCount

02/01/2020             03/05/2020                     -                                      2

How can I achieve this? Is this something I would need to implement in the load script? Or can I do it through an expression?

Thank you for your patience with my newbness and for your help!

[AndrewHughes]

It sounds like you would like to identify the number of times an item has been serviced and the columns are as follows. Service Date 1, Service Date 2, Service Date 3, and on and on. This table needs to have a cross table applied to it. That way it reads like this.
[Item Service Date
1 1/1/2019
1 5/5/2019
2 1/1/2019]
Then you can do an expression like COUNT([Service Date]).

[bundleminion · Accepted solution]

Hey thanks for your help but I ended up solving it by doing one of these in the load script:

temp_crap:
Load ID, CLAIMNO, ServiceDate1 as Date Resident TableName;
Load ID, CLAIMNO, ServiceDate2 as Date Resident TableName;
Load ID, CLAIMNO, ServiceDate3 as Date Resident TableName;
Load ID, CLAIMNO, ServiceDate4 as Date Resident TableName;
Load ID, CLAIMNO, ServiceDate5 as Date Resident TableName;

temp_crap_aggr:
LOAD ID, CLAIMNO, Count(distinct Date) -1 as DateCount Resident temp_crap Group by ID, CLAIMNO;

left join (TableName)
LOAD ID, CLAIMNO, DateCount
RESIDENT temp_crap_aggr;

DROP TABLE temp_crap;
DROP TABLE temp_crap_aggr;

This gave me a field (DateCount) with a total count of dates in the series of date columns (ServiceDate1 - Service Date5).