cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HarryT Frequent Visitor
Frequent Visitor

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:

Column = MIN(HISTORY_ENTRY[ENTRY_DATE].[Date])
 
I just don't understand the 'where" and 'and' and 'or' statements.  They don't pop up when entering them.
 
Any assistance or point me in the right direction is much appreciated.
Thanks!
1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

Re: Creating a column using different clauses and like statements

@HarryT 

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

Final Table.png

Here's the file

https://1drv.ms/u/s!Amqd8ArUSwDS1TmH8k5RklCseY-E?e=mJ9A6A

3 REPLIES 3
Nick_M New Contributor
New Contributor

Re: Creating a column using different clauses and like statements

can you load some sample data and what the goal output would be?

HarryT Frequent Visitor
Frequent Visitor

Re: Creating a column using different clauses and like statements

Hi Nick,

 

Here is some sample data from the HISTORY_ENTRY table.

 

JOB_TICKET_IDENTRY_DATEENTRY_TEXT
2279255/30/2019 9:40Status changed from Open to Closed
2279255/30/2019 9:40Added new tech note.
2279255/30/2019 9:31Reassigned-Ticket E-Mail sent to Joe Smith
2279255/30/2019 8:03Request Type changed from Business Systems 
2279255/30/2019 8:03Status changed from Open to In Progress
2279255/29/2019 18:19Created by Molly Davis
2279255/29/2019 18:19Assigned to Joe Smith
2279225/27/2019 8:03Status changed from Open to Closed
2279225/22/2019 8:03Status changed from Open to Resolved
2279225/21/2019 18:19Created by Molly Davis
2279225/21/2019 18:19Assigned 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_IDENTRY_DATEFirst_Resolution_DateENTRY_TEXT
2279255/30/2019 9:405/30/2019 9:40:14 AMStatus changed from Open to Closed
2279255/30/2019 9:405/30/2019 9:40:14 AMAdded new tech note.
2279255/30/2019 9:315/30/2019 9:40:14 AMReassigned-Ticket E-Mail sent to Joe Smith
2279255/30/2019 8:035/30/2019 9:40:14 AMRequest Type changed from Business Systems 
2279255/30/2019 8:035/30/2019 9:40:14 AMStatus changed from Open to In Progress
2279255/29/2019 18:195/30/2019 9:40:14 AMCreated by Molly Davis
2279255/29/2019 18:195/30/2019 9:40:14 AMAssigned to Joe Smith
2279225/27/2019 8:035/22/2019 8:03:25 AMStatus changed from Open to Closed
2279225/22/2019 8:035/22/2019 8:03:25 AMStatus changed from Open to Resolved
2279225/21/2019 18:195/22/2019 8:03:25 AMCreated by Molly Davis
2279225/21/2019 18:195/22/2019 8:03:25 AMAssigned 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!

Nick_M New Contributor
New Contributor

Re: Creating a column using different clauses and like statements

@HarryT 

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

Final Table.png

Here's the file

https://1drv.ms/u/s!Amqd8ArUSwDS1TmH8k5RklCseY-E?e=mJ9A6A