Flag for Max Amount Group

[krmvacar]

Hi all,

In the table structure below, I group according to the Date, ID, PRODUCTID fields and flag the RESULT field that comes the most.However, if the PRODUCTID field is the largest one in total, not the max one I want here, I want to flag them.

I want the NEW_flag field here.

your help is welcome

Scripts:

test:
load * Inline [
Date , ID ,PRODUCTID ,RESULT
03.01.2022 ,17027 ,E505HK01 ,14.51
03.01.2022 ,17027 ,E505HK02 ,13.96
03.01.2022 ,17027 ,E505HK01 ,9.51
04.01.2022 ,17027 ,E505HK03 ,11.15
04.01.2022 ,17027 ,E505HK02 ,14.94
04.01.2022 ,17027 ,E505HK02 ,8.14
];

left Join(test)
load
Date,
ID,
max(RESULT) as RESULT,
Max(RESULT) AS NewResult,
1 as flag
Resident test
group by Date, ID;

Date	ID	PRODUCTID	Sum(RESULT)	flag	NEW_flag
03.01.2022	17027	E505HK01	14.51	1	1
03.01.2022	17027	E505HK01	9.51	-	1
03.01.2022	17027	E505HK02	13.96	-	0
04.01.2022	17027	E505HK02	14.94	1	1
04.01.2022	17027	E505HK02	8.14	-	1
04.01.2022	17027	E505HK03	11.15	-	0

[Mark_Little]

Hi

Can you try explaining a little more about what you are after, i don't understand what you mean flag what comes the most. Looking at the table i can see you have flag two of each of the groups, but even from that can't understand how?

[Mario_De_Felipe · Accepted solution]

I think you want something like this:

Test:
load * Inline [
Date , ID ,PRODUCTID ,RESULT
03.01.2022 ,17027 ,E505HK01 ,14.51
03.01.2022 ,17027 ,E505HK02 ,13.96
03.01.2022 ,17027 ,E505HK01 ,9.51
04.01.2022 ,17027 ,E505HK03 ,11.15
04.01.2022 ,17027 ,E505HK02 ,14.94
04.01.2022 ,17027 ,E505HK02 ,8.14
];

MaxResultPerDay:
mapping
LOAD
Date,
Max(Num#(RESULT,'#,##0.00','.',',')) as MaxRESULT
RESIDENT Test
GROUP BY Date;

ProductWithMaxResultPerDay:
mapping
LOAD
distinct
PRODUCTID & '_' & Date,
1 as FlagMaxResult
RESIDENT Test
WHERE Num#(RESULT,'#,##0.00','.',',') = ApplyMap('MaxResultPerDay',Date);
RENAME TABLE Test TO Test_Temp;

Test:
Load
*,
ApplyMap('ProductWithMaxResultPerDay',PRODUCTID & '_' & Date,0) as NEW_flag
RESIDENT Test_Temp;
DROP TABLE Test_Temp;