append rows

meadow_ryan
meadow_ryan Member
in Magic ETL

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

Best Answer

  • AS
    AS Coach
    Answer ✓

    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"

Answers

  • AS
    AS Coach
    Answer ✓

    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"
  • meadow_ryan
    meadow_ryan Member

    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
    meadow_ryan Member

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

  • meadow_ryan
    meadow_ryan Member

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

  • meadow_ryan
    meadow_ryan Member

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

  • AS
    AS Coach

    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"
  • DataMaven
    DataMaven Coach

    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
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories