Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Need to move a field from header record to detail record when its blank on detail record

mdorio
7 - Meteor

Hello,

 

I have the following data problem, and hoping to get some advice.

  • I have a file that has header records, detail records, and trailer records.
  • The header records has a transaction date. 
  • The detail records also have a transaction date, but sometimes its missing.
  • When the detail record is missing the transaction date, we want to use the transaction date in the header.

 

I have figured out how to do this, but only for a dataset where there is 1 header.  I have been unable to figure out how to do it using multiple headers.  I was thinking of some kind of batch macro, but couldn't figure out how to group the data to pass it to the batch macro.

 

Any guidance, thoughts, or examples would be greatly appreciated.

 

Mike

 

3 REPLIES 3
T_Willins
14 - Magnetar
14 - Magnetar

Hi @mdorio 

 

Can you provide a sample data set with multiple headers and what data you are looking to extract? 

danilang
19 - Altair
19 - Altair

Hi @mdorio 

 

If you find yourself with multiple header rows you're probably parsing a report that's meant for human consumption and looks something like this

 

1.png

 

You can use this technique to handle these kinds of reports where columns contain interspersed header and/or grouping information

 

w.png

Read in the report as-is and make sure that the 1st row is read a data and not as a header.  Then use a couple of Multi-row tools to first create groups for each transaction and then number the rows within each group.  Use a filter to pull out the rows that represent the start of each transaction and have the date information you need.  Join these rows to the rest of the rows on transaction ID, to append the Date from the transaction line to each of the rows.  Then use a formula tool to replace the dates if they're missing.  

 

At this point you have the dates filled in. If you want to rebuild the original report with the new dates, use a technique similar to the top container.  If you want to continue processing the data and want the fields properly labelled and typed, look at the bottom container that shows one way you could accomplish this.

 

Dan 

 

 

mdorio
7 - Meteor

Thank you so much Dan! This looks exactly what I am trying to do.  Really appreciate it!

 

Mike

 

Labels