topic Re: Calculate sum if field exists in App Development

Calculate sum if field exists

joshakehurst — Fri, 28 Jul 2017 16:12:23 GMT

I need to update my load script to perform the following. I have two fields one which is always assigned to a record (Time) and a second field (compressedTime) which could exist on the record.

1) Check if a field exists (compressedTime) on the record and calculate the sum.

2) If the field DOES NOT exist on the record then calculate the sum of another field (Time).

3) Sum of the two fields based on the above criteria.

Re: Calculate sum if field exists

sonkumamon — Fri, 28 Jul 2017 16:27:53 GMT

In both fields - is the non exists values defined as NULL?

Re: Calculate sum if field exists

joshakehurst — Fri, 28 Jul 2017 16:33:04 GMT

Unfortunately no - we use a non-relational DB, so the field either exists on the record or not. We don't insert this field on every record and then assign a NULL value if empty.

Re: Calculate sum if field exists

sonkumamon — Fri, 28 Jul 2017 19:21:06 GMT

I can't understand your data structure, are the fields in different tables?

Can you please share your original table so I can help more?

Re: Calculate sum if field exists

joshakehurst — Mon, 31 Jul 2017 22:51:28 GMT

All the fields are in the same table.

Re: Calculate sum if field exists

sonkumamon — Tue, 01 Aug 2017 13:58:27 GMT

Try:

If(ISNULL(compressedTime), Time, compressedTime) AS CurrectTime

AND then you can sum the calculated field (CurrectTime).

Re: Calculate sum if field exists

mjtaft2017 — Tue, 01 Aug 2017 17:45:10 GMT

I think this is what you want. If the compressed time is there you want to sum record time and compressed time otherwise just sum the record time.

TimeProblem:

load * Inline [

rtime, compressedTime

02:35:00, 01:30:00

10:00:00,

03:30:30, 00:15:00

04:45:00, 02:20:00

];

Re: Calculate sum if field exists

joshakehurst — Tue, 01 Aug 2017 17:46:43 GMT

Thanks for the feedback Mary!