append rows

meadow_ryan

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

AS

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? 

AS

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? 

meadow_ryan

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

meadow_ryan

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

meadow_ryan

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

meadow_ryan

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

AS

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.

DataMaven

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.