topic count records with same dates to give yes/no flag in QlikView

count records with same dates to give yes/no flag

dpc1 — Wed, 17 Aug 2022 13:03:26 GMT

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

Re: count records with same dates to give yes/no flag

Bill_Britt — Wed, 17 Aug 2022 19:10:07 GMT

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.

Re: count records with same dates to give yes/no flag

MarcoWedel — Wed, 17 Aug 2022 22:01:19 GMT

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

Re: count records with same dates to give yes/no flag

sidhiq91 — Thu, 18 Aug 2022 03:45:19 GMT

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

Thanks in advance.

Re: count records with same dates to give yes/no flag

MarcoWedel — Thu, 18 Aug 2022 21:29:36 GMT

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?

Re: count records with same dates to give yes/no flag

sidhiq91 — Fri, 19 Aug 2022 02:20:10 GMT

@MarcoWedel Thanks. Appreciate your explanation as always.

Re: count records with same dates to give yes/no flag

dpc1 — Wed, 24 Aug 2022 12:08:49 GMT

thank you just what we need 😊