Auto fill column with zero when combining data sets in Data Flow

I have two data sets that I am joining through Data Flow.  The data is being joined by SKU and their respective quantities in different locations in our warehouse.  Ideally, if the SKU exists in one location and not the other, I need that quantity to populate 0 - not a blank as it currently does.  If that doesn't work, is there a way to create a Data Set similar to using VLOOKUP in excel?  Thanks, Matt

 

 

Comments

  • Hi all,


    Can anybody help @mwade with their question?

    Thanks!

  • Matt,

     

    Putting in a default value of 0 if there is not a match is something that can be done in the dataflow using joins and the IFNULL() function. We'll want to simulate a FULL OUTER JOIN by doing a LEFT JOIN and a RIGHT JOIN and then unioning them together. Here's a generic example:

     

    Assume we have two tables, table1 and table2. Each table has two fields: id and value. the following UNION will grab all the values from table1, join them to  values from table2 and put in zeros anywhere a null value is found, then do the same thing going the other direction - values from table2 being joined to table1. Unioning the two together gives us the results of both sets.

     

    SELECT
        t1.id
        , IFNULL(t1.value, 0) AS `Value1`
        , IFNULL(t2.value, 0) AS `Value2`

    FROM
        table1 t1
        LEFT JOIN table2 t2 ON t1.id = t2.id

    UNION


    SELECT
        t2.id
        , IFNULL(t1.value, 0) AS `Value1`
        , IFNULL(t2.value, 0) AS `Value2`

    FROM
        table 1 t1
        RIGHT JOIN table2 t2 ON t1.id = t2.id

     

     

    Swapping out your tables and values (and adding other fields to the select list as necessary) should let you do the same thing with your data.

     

    Let me know if that helps!

     

  • @mwade, did zcameron's reply help answer your question?