Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm pulling data into a table and trying to create a value to show if a maintenance task has been completed on a single date.
so in the example below i have the maintenance request code, engineer and dates they finished. where the dates are all the same for each request i want so show that's its been fixed on the same day, where a request had different dates for the engineer visits it was not completed on the same day.
So i'm trying to create an expression to give a yes/no to show if the maintenance request was a first day fix, so it should look like the table below. (the first 3 columns are fields/dims from the load script, the 4th column I'm trying to create in the table expressions)
can anyone help with an expression to write for this, thank you
Work Request Code |
Engineer |
Date Finished |
First day Fix |
2391162 |
JAMES VEVERS |
05/07/2022 |
Yes |
2391162 |
LUKE ELLIOT |
05/07/2022 |
Yes |
2391162 |
NICHOLAS CANAVAN |
05/07/2022 |
Yes |
2391171 |
BOB FAIRCLOUGH |
01/07/2022 |
No |
2391171 |
BOB FAIRCLOUGH |
06/07/2022 |
No |
2391171 |
KENAN ROBINSON |
01/07/2022 |
No |
2391171 |
KENAN ROBINSON |
06/07/2022 |
No |
One solution using a straight table expression might be:
If(Count(DISTINCT TOTAL<[Work Request Code]> [Date Finished])=1,'Yes','No')
hope this helps
Marco
Hi,
I would think you would need another column for the StartDate. Then you can use an if statement.
if([Date started]=[Date Finished] ,'yes','no') as[First day Fix]
See attached QVW.
One solution using a straight table expression might be:
If(Count(DISTINCT TOTAL<[Work Request Code]> [Date Finished])=1,'Yes','No')
hope this helps
Marco
@MarcoWedel Could you please explain what this expression actually does?
Thanks in advance.
The expression assigns a value of 'Yes' to all Work Request Codes having only one single distinct Date Finished and a value of 'No' to all other Work Request Codes.
@dpc1 , is this your expected result?
@MarcoWedel Thanks. Appreciate your explanation as always.
thank you just what we need 😊