GROUP BY - not filtering Expected Results

[suby]

Hi 

I have attached the workflow where by i m using the GROUP BY and MAX function but when i filter on my output I'm not getting expected result.

Expected Output

when i filter for Max_SP Document CHECK ='YES' it should be bring only the SUB PROD STATUS ='LIVE'

Any thoughts Please.

Thanks

S

[mdsalmankhan89]

Hi @suby ,

Not entirely sure about the intent of MAX here.

However in terms of functionality if we see the entire product line A get the value 'YES' (due to alphabetical sort and max of 'SP Document CHECK' for entire product line) for 'Max_SP Document CHECK' and thus you are seeing the expected behavior. 

Regards,

Md. Salman Khan

[suby]

Apologize for not explaining in detail.

The intent of MAX is

Since my A1 satisfies all the condition like

SUB PRODUCT STATUS = LIVE
SP DOC C = FIRST
SP DOC SC = OK

so I'm using MAX on SP DOCUMENT CHECK GROUPED BY PROD ID_3 to be treated my siblings ( A2 & A3 ) as YES and A4 as NO because  A4 has a SUB PRODUCT STATUS = OPEN which is different from A1,A2& A3.

But the Issue is
MAX SP DOCUMENT CHECK treats A4 as YES even though my

SP DOCUMENT CHECK column has the below condition.

IF
[SUB PROD STATUS CHECK] = 'YES' AND
[SP DOC C] = "FIRST" AND
[SP DOC SC] ="OK" THEN 'YES'
ELSE 'NO' ENDIF

[Qiu]

@suby 
I was not able to fully understand the logic here, but would a filter tool works here?

[suby]

Hi Qiu,

Thanks for your reply much appreciated i don't want to restrict my dataset by putting a filter.

Let me Explain this..

after i ran my workflow I'm filtering on the Max_SP Document CHECK = YES on my output file.

SUB PROD STATUS CHECK  - On this I'm checking if my IF [SUB PRODUCT STATUS]= 'LIVE' THEN 'YES' ELSE 'NO' ENDIF

SP Document CHECK - On this I'm checking if my IF [SUB PROD STATUS CHECK] = 'YES' AND [SP DOC C] = "FIRST" AND
[SP DOC SC] ="OK" THEN 'YES' ELSE 'NO' ENDIF

Then On my summarize I'm grouping by PROD ID _3 column and  doing a MAX on SP Document CHECK..( Why am i doing a MAX here is if any one of my  SUB PRODUCT_5 ( which is A1, A2,A3,A4)  satisfies  the below condition

SUB PRODUCT STATUS	SUB PROD  ID DOC	SP DOC C	SP DOC SC
LIVE	A1	FIRST	OK

in this case (A1) Satisfies the above condition so  treat my other siblings which is A2,A3,A4, =YES. which is fine up to this point.

Issue- 

So when i filter on Max_SP Document CHECK on my output file i would expect to see only three rows which A1,A2,A3, 

A4 should be ignored since A4 SUB PRODUCT STATUS is not LIVE.

Hope this helps let me know if you need any additional information.

[mdsalmankhan89]

Hi @suby , 

Can you try using 'SUB PRODUCT STATUS' in the group by along with 'PROD ID _3' in your MAX and subsequently use that in the join condition.

Best Regards,

Salman

[suby]

Hi,

Thanks for your reply just to keep it simple raising a new thread.

Thanks