append rows

Reply
Orange Belt

append rows

Hello all,

 

I have a question on an append rows which isn't working, I have a field, equipment_item which is used for three different things, a Driver, a Tractor, and a Trailer, I can filter out which of those I'm needing based on the equipment_type_id being either, D, T, L respectively, but after I do my append and then join back in with the rest of my data flow nothing is coming up and am wondering if I'm doing the append corrrectly....I have attached screenshots for each step of my append


Accepted Solutions
Black Belt
Black Belt

In your append it looks like you have 6 new columns for each equipment and movement ID.  These columns aren't all simultaneously populated in each row, so when you try to join the data on three values where only one exists (a row of data doesn't have a trailer, driver, and tractor ID, it only has one of them because of the way you've pulled it apart into three pieces and appended everything together), it's not finding matches.  That's my theory.

When you run the data and look at the preview at each step, does the data look like you have anticipated? 

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Black Belt
Black Belt

In your append it looks like you have 6 new columns for each equipment and movement ID.  These columns aren't all simultaneously populated in each row, so when you try to join the data on three values where only one exists (a row of data doesn't have a trailer, driver, and tractor ID, it only has one of them because of the way you've pulled it apart into three pieces and appended everything together), it's not finding matches.  That's my theory.

When you run the data and look at the preview at each step, does the data look like you have anticipated? 

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Orange Belt

the only preview which shows anything is my first select columns, when I start filtering the rows by equipment_type_id = 'D' for driver, 'T' for Tractor and 'L' for Trailer nothing comes up

Orange Belt

so I fixed that by adding a replace text as there must've been whitespace around my equipment type id

Orange Belt

but on my append rows preview my driver, tractor, and trailer all are on different rows if you look at the attachement

Orange Belt

I got if figured out....basically the whitespace was the whole problem...thanks for the extra set of eyes

Black Belt
Black Belt

Was the separated rows by equipment intentional?  I was thinking you wanted them all on the same rows, given each type had its own column.

Good sleuthing.  It would have been difficult to resolve without testing that whitespace.

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Black Belt

You probably need to do a group by after your append to get them all on the same rows.  The "first non null value" function has saved me many times!  

 

I can't look at the attachments from my phone, so that's my blind answer, having bumped into similar issues.  

DataMaven
Breaking Down Silos - Building Bridges
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
New & Improved Dojo: Get excited for a new feel and additional features in the online community.

Site will be down February 4-5th for maintenance.

Launch coming February 8th!