Conditional recode

Solved!
emate
Level 5
Conditional recode

Hi All,

Is there a way to recode specific string value based on other column values? for exmaple I have 1000 rows, I know that out of all those I want to recode only specfic products (in my case I have 2 products with the same name, and I want to make them distinct adding a group for them (but only for them). Something like on a screen below:

 

emate_0-1588227914548.png

Thank you,

eM

2 Solutions
emate
Level 5
Author

OK, nevermind.... ๐Ÿ™‚ 

if (Products=="A" && GROUP=="1", 'A', 'A1')

if (Products=="A" && GROUP=="2", 'A', 'A2')

 

*** OR maybe no.... when I used this formula in my 'real data' it recoded all values in Product Column to a A1.. hmm.

 

*** LAST UPDATE - sorry I already found the solution:

if(condition, then, else) so the syntax will be:

 

if(Group=="1", replace(Products, "A", "A1"),Products)

View solution in original post

VinceDS
Dataiker

Hi, 

To do this you will need to go through 2 visual recipes. The first step is to do a Window recipe to count the number of rows for a specific Product code. 

Open a window recipe and set it up as below - Partitioning on your Product column and Count aggregation on your Product column (here it's Col1 in my example)

Screenshot 2020-04-30 at 09.46.28.png

You will end up with a dataset that should look like this: 

Screenshot 2020-04-30 at 09.47.48.png

From there, open a Prepare recipe and use a Formula processor to create your Products after recode with a syntax like below: 

Screenshot 2020-04-30 at 09.50.01.png

Hope this helps

View solution in original post

3 Replies
emate
Level 5
Author

OK, nevermind.... ๐Ÿ™‚ 

if (Products=="A" && GROUP=="1", 'A', 'A1')

if (Products=="A" && GROUP=="2", 'A', 'A2')

 

*** OR maybe no.... when I used this formula in my 'real data' it recoded all values in Product Column to a A1.. hmm.

 

*** LAST UPDATE - sorry I already found the solution:

if(condition, then, else) so the syntax will be:

 

if(Group=="1", replace(Products, "A", "A1"),Products)

VinceDS
Dataiker

Hi, 

To do this you will need to go through 2 visual recipes. The first step is to do a Window recipe to count the number of rows for a specific Product code. 

Open a window recipe and set it up as below - Partitioning on your Product column and Count aggregation on your Product column (here it's Col1 in my example)

Screenshot 2020-04-30 at 09.46.28.png

You will end up with a dataset that should look like this: 

Screenshot 2020-04-30 at 09.47.48.png

From there, open a Prepare recipe and use a Formula processor to create your Products after recode with a syntax like below: 

Screenshot 2020-04-30 at 09.50.01.png

Hope this helps

emate
Level 5
Author

Thank you for another way to approach this.

As for now, I think, since I know what specific product I am looking for, and that won't change I guess I can stick to my solution? Your way is much safer if I would not have idea how much products are like that, and when that can change over time.