Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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.
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;
The field has being created named Combined Field in the script provided.
You just need to call that field out in the table.
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
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