topic Re: How to perform calculations on a new field from a Join in App Development

How to perform calculations on a new field from a Join

Sam_Thomas — Wed, 29 Mar 2023 11:28:12 GMT

The title probably makes no sense, but I don't know how else to put it.

I have a fairly simple data set, which is just records of maintenance jobs performed on pieces of equipment.

I recently came here for help on pulling only the latest records for each piece of equipment, which was solved (using the join function at the bottom of my script) but now I am not sure how I would perform count and sum calculations etc. as the way I am doing it now is returning incorrect figures.

For example. if I want to count how many pieces of equipment are 'out of date' ([OOD FLAG]), how can I make sure it's only counting based on the latest date (max_date)?

Please see my full script below (with irrelevant fields omitted):

MaxDate:
LOAD
num("Component Equip Numb") as "Component Equip Numb" ,
"Equipment Name Code",
"Component Asset Stat",
"SCHEDULED FREQUENCY",
"Stat Date",
"Work Group",

if(date(today()>([Stat Date]+[SCHEDULED FREQUENCY])), '1', '0') as [OOD Flag]

FROM [lib://SourceDataFiles/xxx.qvx]
(qvx);

RIGHT JOIN (MaxDate)
LOAD
    num("Component Equip Numb") as "Component Equip Numb",
    "Work Group",
    "Component Asset Stat",
    DATE(MAX("Stat Date"), 'DD/MM/YYYY') As Max_Date
Resident MaxDate
WHERE MATCH ("Work Group", 'AM') AND MATCH ("Component Asset Stat", 'IN','MB')
GROUP BY "Component Equip Numb", "Work Group", "Component Asset Stat";

Thanks

Re: How to perform calculations on a new field from a Join

BrunPierre — Sat, 01 Apr 2023 16:38:17 GMT

Hello, the resultant table will only contain the most recent records with matching records from Table MaxDate.

Hence you could use the below.

Count({$<[OOD Flag]={1}>} DISTINCT [Component Equip Numb])

Re: How to perform calculations on a new field from a Join

Sam_Thomas — Wed, 29 Mar 2023 14:42:36 GMT

Thanks for this. I did try this expression before I asked the question on here and got the same answer, however I assumed it was wrong because the number was way higher than I thought. However, there appears to be another data issue at source level that is skewing the figures rather than the expression! Thanks for your answer, nonetheless!