Added new column with Max Value and Concatenation

[Mateo31]

Hi everyone,

I have the following table:

Product_ID	Rate	Category
LENUSF5	3	B
LENUSF5	2	B
LENUSF6	4	C
LENUSF7	3	B
LENUSF8	3	B
LENUSF9	3	B
LENUSF10	3	B
LENUSF11	1	A
LENUSF12	6	D
LENUSF13	10	E
LENUSF14	1	A
LENUSF15	1	A
LENUSF16	1	A
LENUSF16	2	A

I need to create a new field with the Combination of the columns Rate and Category by Product_ID(example: 3 and B become 3B). 

But If there are more than one Rate per Product_ID, I just need to use the larger Rate value (example: use 3B instead of 2B for LENUSF5)

Desire result

Product_ID	Rate	Category	Combined_field
LENUSF5	3	B	3B
LENUSF5	2	B	Don’t show
LENUSF6	4	C	4C
LENUSF7	3	B	3B
LENUSF8	3	B	3B
LENUSF9	3	B	3B
LENUSF10	3	B	3B
LENUSF11	1	A	1A
LENUSF12	6	D	6D
LENUSF13	10	E	E10
LENUSF14	1	A	1A
LENUSF15	1	A	1A
LENUSF16	1	A	Don’t show
LENUSF16	2	A	2A

Thanks in advance.

Mateo.

[Arthur_Fong]

Try this at script level:

Raw:
LOAD * INLINE [
Product_ID,Rate,Category
LENUSF5,3,B
LENUSF5,2,B
LENUSF6,4,C
LENUSF7,3,B
LENUSF8,3,B
LENUSF9,3,B
LENUSF10,3,B
LENUSF11,1,A
LENUSF12,6,D
LENUSF13,10,E
LENUSF14,1,A
LENUSF15,1,A
LENUSF16,1,A
LENUSF16,2,A
];
NoConcatenate
tempData:
load Product_ID,max(Rate)as Rate,Category
resident Raw
group by Product_ID,Category
;
drop table Raw;

Data:
load *,Rate&Category as [Combined_Field]
resident tempData;
drop table tempData;

[lblumenfeld]

I'm assuming that the Category is always the same for the Product_ID. So, this statement should work.

YourTable:
Load
   Product_ID,
   Category,
   Max(Rate) as Rate
Resident YourSourceTable     // or From wherever you have the data.
Group By
   Product_ID,
   Category   

Let me know if that works.

[Mateo31]

Thanks for your help.

With your solution I was able to find the Max Rate. But, I also need to create a new field with the combination of Max rate and Category.

New_Field: 10E

Where 10 is (Max_Rate) and E (Category).

Thank you.

[StarinieriG]

Hi,

try with something like this

TABLE:

LOAD * FROM....;

Left Join
LOAD
Product_ID,
Max(Rate) as Rate,
1 as Combined_field_temp
Resident TABLE
Group By Product_ID
;

NoConcatenate
OUTPUT:
LOAD
Product_ID,
Rate,
Category,
If(Combined_field_temp=1,Rate & Category) as Combined_field
Resident TABLE;

DROP TABLE TABLE;

[Arthur_Fong]

The field has being created named Combined Field in the script provided. 

You just need to call that field out in the table.

[lblumenfeld]

Oops... forgot to add that... This is all yo need.

YourTable:
Load
   *,
   Rate & Category as RateCategory;
Load
   Product_ID,
   Category,
   Max(Rate) as Rate
Resident YourSourceTable     // or From wherever you have the data.
Group By
   Product_ID,
   Category   

[Brett_Bleess]

Mateo, it seems you may have the solution at this point, I just wanted to remind you to be sure to use the Accept as Solution button on the post(s) that you needed to get things working.  This gives credit to those that wrote those up and it lets others know what actually worked for you.  If you are still working on things even after the last few new posts, leave another update when you can.

Regards,
Brett