Forum Discussion
Creating a column using different clauses and like statements
Hi everyone,
We are in the process of moving some dashboards from QlikView to PBI. Right now we are trying to recreate this field / column in PBI.
Here is the load statement in Qlik:
first_res_date = (select min(entry_date)
from "whd_prod".dbo."HISTORY_ENTRY" he
where he.JOB_TICKET_ID = jt.JOB_TICKET_ID
and (he.ENTRY_TEXT like '%to Resolved'
or he.ENTRY_TEXT like '%to Closed'))
What this is is creating a first resolution date. Whether is it put in resolved or closed first. Using this field helps with math later on in determining timing to resolved/closed from reported dated.
So far I have this in PBI:
I went ahead and did this all in Power Query, so I attached the pbix file below so you walk through each step and see what I did. Too much to explain all here as it's much easier to just see it, but let me know if you have any questions. One thing, I did split EntryDate column into two columns, one for date and one for time. The way compression works you generally dont want those values combined.
Final table
Here's the file
3 Replies
Hi Nick,
Here is some sample data from the HISTORY_ENTRY table.
JOB_TICKET_ID ENTRY_DATE ENTRY_TEXT 227925 5/30/2019 9:40 Status changed from Open to Closed 227925 5/30/2019 9:40 Added new tech note. 227925 5/30/2019 9:31 Reassigned-Ticket E-Mail sent to Joe Smith 227925 5/30/2019 8:03 Request Type changed from Business Systems 227925 5/30/2019 8:03 Status changed from Open to In Progress 227925 5/29/2019 18:19 Created by Molly Davis 227925 5/29/2019 18:19 Assigned to Joe Smith 227922 5/27/2019 8:03 Status changed from Open to Closed 227922 5/22/2019 8:03 Status changed from Open to Resolved 227922 5/21/2019 18:19 Created by Molly Davis 227922 5/21/2019 18:19 Assigned to Joe Smith
So the idea here would be getting a new field called something like "First_Resolution_Date"This would be the date the first time you see either "To Resolved" or "To Closed" in the history entry notes.
In this example:
the First_Resolution_Date for Job_Ticket_ID 227925 would be 5/30/2019 9:40:14 AM
the First_Resolution_Date for Job_Ticket_ID 227922 would be 5/22/2019 8:03:25 AM (note this one has both a "To Resolved" and a "To Closed", but the "To Resolved" is earlier.
Here is how this table should look:
JOB_TICKET_ID ENTRY_DATE First_Resolution_Date ENTRY_TEXT 227925 5/30/2019 9:40 5/30/2019 9:40:14 AM Status changed from Open to Closed 227925 5/30/2019 9:40 5/30/2019 9:40:14 AM Added new tech note. 227925 5/30/2019 9:31 5/30/2019 9:40:14 AM Reassigned-Ticket E-Mail sent to Joe Smith 227925 5/30/2019 8:03 5/30/2019 9:40:14 AM Request Type changed from Business Systems 227925 5/30/2019 8:03 5/30/2019 9:40:14 AM Status changed from Open to In Progress 227925 5/29/2019 18:19 5/30/2019 9:40:14 AM Created by Molly Davis 227925 5/29/2019 18:19 5/30/2019 9:40:14 AM Assigned to Joe Smith 227922 5/27/2019 8:03 5/22/2019 8:03:25 AM Status changed from Open to Closed 227922 5/22/2019 8:03 5/22/2019 8:03:25 AM Status changed from Open to Resolved 227922 5/21/2019 18:19 5/22/2019 8:03:25 AM Created by Molly Davis 227922 5/21/2019 18:19 5/22/2019 8:03:25 AM Assigned to Joe Smith There is also a master table called "JOB_TICKET" that is linked on the JOB_TICKET_ID and that is why in the orginal query we had:
where he.JOB_TICKET_ID = jt.JOB_TICKET_ID
Maybe it isn't needed in Power BI.
I hope this helps.
Thanks again!
I went ahead and did this all in Power Query, so I attached the pbix file below so you walk through each step and see what I did. Too much to explain all here as it's much easier to just see it, but let me know if you have any questions. One thing, I did split EntryDate column into two columns, one for date and one for time. The way compression works you generally dont want those values combined.
Final table
Here's the file
