Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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).
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).