3 Replies Latest reply: May 11, 2017 6:36 AM by Anat Dagan RSS

    adding values to empty fields on script level

    Anat Dagan

      Hi!

      I have 2 tables:

      Table [ProductID] contains all product details including ListingId (unique identifier) and ProductColor.

      ListingId

      ProductColor

      1

      Beige

      2

      Beige

      3

      Black

      4

      Black

      5

      Black

      6

      Blue

       

      Table [Sales] contains all sales data for sold products including LIstingId and QuantitySold

      ListingId

      QuantitySold

      1

      1

      3

      12

       

      I want to add a “Sold/unsold” flag so I can analyze the unsold items. So I tried to add the “Sold/unsold”  field to the ProductID table so that it looks like this:

      ListingId

      ProductColor

      Sold/unsold

      1

      Beige

      1

      2

      Beige

      0

      3

      Black

      1

      4

      Black

      0

      5

      Black

      0

      6

      Blue

      0

       

      I added the following script after loading the 2 tables but the Sold/unsold Flag shows “-“ for the unsold items so I can’t use it in my analysis.

       

      Join (productID)load [ListingId],

      if(LEN(Trim(QuantitySold))=0,0,1) as [sold/unsold] resident Sales;

       

      I  also tried:

       

      Join(productID)load [ListingId],

      if(IsNull(QuantitySold),0,1) as [sold/unsold] resident Sales;

       

      This what the data looks like:

       

       

      Where did I go wrong?