We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Number to string and copy data to a record

payntet
5 - Atom

I have no idea even how to start this. 

1st step I need to take Role producer staff "Y" and make all the articles null.  The reason to make them null incase a 1 or 0 fell into the record when I was building the other process.  

2nd step - next need to take Producer_staff_parent_customer_Id  and match with the Customer_ID if match then 

3rd step - Update producer staff record with 1 and 0 according to what the customer_ID has (so the staff gets the same articles as who he/she is working for)

My articles from efix_4267 to EFIX_5855  - these articles do not change they are in every issue of the newsletter.  They might have different 1 and 0 each week.  I was try to write something that I do not have to change each week

 

Then the rest of the articles after EFIX_5855 change each week.  So I would have to add these in each week according to the articles names.

 

Then

I need to take the entire list.  Role_agent "Y" or Role_Rep "Y" or Role_Producer_Staff "Y"  or don't even have to write this because the entire list I need to convert each articles from a 1 to a "Y" and 0 to a null.  

 

Articles names EFIX4267 to EFIX_5855 do not change each week.  Then the rest of the articles change each week. I have to keep the name of the articles as stated because the code to send the email is set up according to article names.    

 

Can you give me some suggestion on how to do this? 

 

Thanks

 

 

 

5 REPLIES 5
grazitti_sapna
17 - Castor

HI @payntet , is it possible for you to share a desired output that is required also I was able to solve your first step.

grazitti_sapna_0-1602479073943.png

 

What you need to do is to use transpose and cross tab to make the values null/empty.

 

Now for step 2 are you trying to join the records with the records where role producer staff =Y?

 

Thanks.

Sapna Gupta
payntet
5 - Atom

@grazitti_sapna 

 

Master table - is everyone.  (The producer staff records match what his/her boss is going to receive. Thinking about this this step could be the last step after everything has gone through the audit. See comment following. )  This master table has everyone and is with 0 and 1.  (master table).  This list has 5 other roles listed but it is exactly like the agt_rep but with different roles and the same column.  What I have to do is split out the list bo_fmo (which is not listed roles on spreadsheet) and agt_rep.  This list is sent over to my proofer and he makes sure I mark everything according to the company rules.  If he comes back with changes I go back into my flow and update the 1 and 0 accordingly and send him the new lists (bo_fmo, agt_rep).  When he signs off then I am able to update the producer staff records to what his/her boss has.  

 

If everyone is updated in the master table  except producer staff then I can update the entire table with Y for the 1 and null for the 0.  (test covert to Y csv)

 

Then for my send I will split out into 2 files.  Role_rep, role_agent and another file for Role_producer staff.  (agt_rep Table, test covert to Y producer staff)

 

If there is a better way to do this I am open.  It is a lot of back and forth until you get the final approval.  

 

Thanks

grazitti_sapna
17 - Castor

Hi @payntet , as you said of the columns such as bo_fmo is not in the spreadsheet so looking at your test convert sheets what I understood I tried to sort it in a shorter way.Give this a try and let me know what do you think?

What I have done is I have picked the column values from the agt table joining with the master table and the records that did not join with the agt table then transformed the 0 and 1 values.

Thanks.

Sapna Gupta
payntet
5 - Atom

Thanks @grazitti_sapna 

I wouldn't have gotten this far without your help.  

I have a few questions within the Community question 1.  

I hope that I followed your directions and get results that you expected.  

If you see any improvements please let me know.  

 

Thanks

grazitti_sapna
17 - Castor

Hi @payntet ,I have made the required changes and added my solutions to your problems with comments in green. I hope this helps.

Thanks.

 

Sapna Gupta
Labels