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

mwade

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

 

 

kshah008

Hi all,

Can anybody help @mwade with their question?

Thanks!

zcameron

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!

 

kshah008

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