topic Re: Set Analysis with OR and MAX condition in App Development

Set Analysis with OR and MAX condition

Badzreyes00 — Sat, 16 Nov 2024 18:46:59 GMT

Good day Everyone!

I am having trouble using Set Analysis and I hope you can help me with my issue:

I have a requirement where:

1. Sum of trxn_amt of all SE

2. SE Rule

EMP_FIELD = 'SE' AND INC_FIELD NOT 'SAL'

EMP_FIELD NOT 'SE' AND INC_FIELD =' BI' and OCC_FIELD like '*BO*' (Because there are values like BO-AA, BO-BB so I need to capture all 'BO')

And below is my set analysis:

sum(
{<trxn_desc={'LD'},serial_no={"=$(max(serial_no))"},EMP_FIELD={'SE'},INC_FIELD-={'SAL'}>+<INC_FIELD={'BI'},OCC_FIELD={"*BO*"}>} trxn_amt)

trxn_desc = LD (my filter for correct type of transaction)

serial_no = There are multiple records for each ID so I need to get the latest record for updated EMP_FIELD, INC_FIELD and OCC_FIELD

Basically with that I need to get all EMP_FIELD=SE but INC_FIELD <> SAL OR

if EMP_FIELD <> SE then INC_FIELD=BI AND OCC_FIELD=BO

I hope that I explained it clearly, thank you so much in advance for your help and have a good day!

Re: Set Analysis with OR and MAX condition

sunny_talwar — Wed, 15 Apr 2020 18:17:43 GMT

Not entirely sure as to what you are getting and what you are looking to get, but try this may be

Sum({<trxn_desc = {'LD'}, serial_no = {"$(=Max(serial_no))"}, EMP_FIELD ={'SE'}, INC_FIELD -= {'SAL'}>+<INC_FIELD = {'BI'}, OCC_FIELD = {"*BO*"}>} trxn_amt)

Re: Set Analysis with OR and MAX condition

Badzreyes00 — Thu, 16 Apr 2020 01:35:23 GMT

Hi Sunny,

Thank you so much for your response.

Actually the problem is all the INC_FIELD and OCC_FIELD that I am getting are BI and BO only but I need to capture all INC_FIELD except SAL and ALL OCC_FIELD IF EMP_FIELD=SE and if NOT SE then INC_FIELD is BI only and OCC_FIELD is like BO only.

EMP_FIELD	INC_FIELD	OCC_FIELD
SE	ALL except SAL	ALL
if not SE	BI only	and like BO only

so if I'll convert the expression after serial no. in IF statement it is something like below:

SUM(IF(

EMP_FIELD='SE' AND INC_FIELD<>'SAL',trxn_amt,

IF(EMP_FIELD<>'SE' AND INC_FIELD='BI' AND OCC_FIELD LIKE '*BO*',trxn_amt,0)))

So I am creating a pivot table with daily trxn_amt based on the conditions above. Thank you so much!

Re: Set Analysis with OR and MAX condition

Badzreyes00 — Fri, 17 Apr 2020 07:49:13 GMT

I just created this in the script editor instead.

1. I created a column for getting the latest record

TMP01:
Load*,
AutoNumber(recno(),cust_no) as s_no
RESIDENT CFB_TMP
ORDER BY cust_no, data_create_time desc;

2. I then created the Tag

Load*,
if(s_no=1 andEMP_FIELD='SE' AND INC_FIELD<>'SAL','Self-Employed',
If(s_no=1 and EMP_FIELD<>'SE' AND INC_FIELD='BI' AND OCC_FIELD LIKE '*BO*','Self-Employed','Employed'))
as "Employed/Self-Employed Tag"

3. And simply use this formula in the front end:

Sum({<trxn_desc={'LD'}[Employed/Self-Employed Tag] = {"Self-Employed"}>} trxn_amt)

Works perfectly except that now my data model is taking too much time to load.

Thank you!