Want to count the number of rounds/MAE_CreateBy , in which 1 round must consist of

[Sxbbb]

I Have Data 

EX  FT_ID = 20201232  and MAE_CreateBy=10002706

Want to count the number of rounds/MAE_CreateBy , in which 1 round must consist of

1. Bottom and Top = Install

2. MAE_Type = Dismantle 

will be 1 round

So FT_ID = 20201232 will be 2 round.

I tried but it's not correct :

[Daniel_Pilla]

Hi @Sxbbb ,

Personally, I would 100% do this in the load script with if statements and a group by, creating a boolean flag field as the result. That said, if you wanted to do it in the front end in a table like you have, you could use the following expression:

Sum(TOTAL <FT_ID,MAE_CreateBy>
  Aggr(
    Count(DISTINCT
      {
        <MAE_CreateBy=P({<MAE_Lavel={'Top'},MAE_Type={'Install'}>})>
        *<MAE_CreateBy=P({<MAE_Lavel={'Bottom'},MAE_Type={'Install'}>})>
        *<MAE_CreateBy=P({<MAE_Type={'Dismantle'}>})>
      } 
      MAE_CreateBy
    ),
    FT_ID,
    MAE_CreateBy
  )
)

Cheers,

[Sxbbb]

'load script with if statements and a group by, creating a boolean flag field ' 

What do I have to do? I don't understand how to write it.

[Sxbbb]

I tried   

EX  FT_ID  = 20020819   Show value 1 round 

But if you select FT_ID  = 20020819  the value will change to 0. 

Must select  FT_ID  value to be correct. 

Where do I need to fix it?

[Daniel_Pilla]

Hi @Sxbbb ,

Here is one way to do it in the load script. The "Sign()" function allows there to be multiple occurrences and it will still resolve to "1". If you only want there to be a single occurrence of each criteria, you can remove that function. Feel free to adjust the logic as needed.

Data:
LOAD 
	*,
    If(MAE_Lavel='Bottom' AND MAE_Type='Install',1) AS Flag1,
    If(MAE_Lavel='Top' AND MAE_Type='Install',1) AS Flag2,
    If(MAE_Type='Dismantle',1) AS Flag3,
    FT_ID & '|' & MAE_CreateBy AS _Key
INLINE [
	FT_ID	,MAE_CreateBy	,MAE_Lavel	,MAE_Type
    20201232,10002706		,Bottom		,Install
    20201232,10002706		,Top		,Install
    20201232,10002706		,			,Dismantle
    20201232,91020815		,Bottom		,Install
    20201232,91020815		,Top		,Install
    20201232,91020815		,Top		,Install
    20201232,91020815		,			,Dismantle
    20201234,91020816		,Bottom		,Install
    20201234,91020816		,Top		,Install
    20201235,10002707		,Bottom		,Install
    20201235,10002707		,			,Dismantle

];

LEFT JOIN (Data)
LOAD
	If(Sign(Sum(Flag1)) + Sign(Sum(Flag2)) + Sign(Sum(Flag3))=3,1,0) AS Flag,
    _Key
RESIDENT Data
GROUP BY _Key;

DROP FIELDS Flag1, Flag2, Flag3, _Key;

 Cheers,

[Sxbbb]

I want to show MAE_Type  at  Max(MAE_CreateDate) 

I tried but it's not correct :