Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bundleminion
Contributor II
Contributor II

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

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!

1 Solution

Accepted Solutions
bundleminion
Contributor II
Contributor II
Author

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

View solution in original post

2 Replies
AndrewHughes
Partner
Partner

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
Contributor II
Contributor II
Author

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

View solution in original post