Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
dpc1
Contributor
Contributor

count records with same dates to give yes/no flag

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

 

 

Labels (3)
1 Solution

Accepted Solutions
MarcoWedel

One solution using a straight table expression might be:

MarcoWedel_0-1660773574246.png

If(Count(DISTINCT TOTAL<[Work Request Code]> [Date Finished])=1,'Yes','No')

 

hope this helps

Marco

View solution in original post

6 Replies
Bill_Britt
Former Employee
Former Employee

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.

Bill - Principal Technical Support Engineer at Qlik
To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
MarcoWedel

One solution using a straight table expression might be:

MarcoWedel_0-1660773574246.png

If(Count(DISTINCT TOTAL<[Work Request Code]> [Date Finished])=1,'Yes','No')

 

hope this helps

Marco

sidhiq91
Specialist II
Specialist II

@MarcoWedel  Could you please explain what this expression actually does?

Thanks in advance.

MarcoWedel

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?

sidhiq91
Specialist II
Specialist II

@MarcoWedel  Thanks. Appreciate your explanation as always.

dpc1
Contributor
Contributor
Author

thank you just what we need 😊