1 Reply Latest reply on Jun 22, 2015 4:58 AM by Tom W

    Merge/union with different type of data source

    Jimmy Yeung

      Hi All,

       

      This is my first post. Glad to be here.

       

      I have a question on merging / union with 2 type of data source: MS SQL table & Excel

       

      ShopInfo: (in MS SQL)

      ShopIDName
      1001AAA Fruit Shop
      1002BBB Fruit Shop
      1003CCC Fruit Shop

      Sale:(in MS SQL)

      ShopIDDateProductUnitPriceQuantityTotalPrice
      10012014/6/1 09:05:22Apple155
      10022015/6/3 21:38:10Melon31030
      10012015/6/6 16:19:07Orange0.81512
      10012015/6/11 23:11:06Apple1.11213.2

      Sale2: (in Excel)

      ShopIDDateProductUnitPriceQuantityTotalPrice
      10032014/6/5 10:55:04Banana2816
      10032015/6/10 13:51:04Banana2.11021

       

      Ideal Result:

      NameYearTotalPrice
      AAA Fruit Shop20145
      AAA Fruit Shop201525.2
      BBB Fruit Shop201530
      CCC Fruit Shop201416
      CCC Fruit Shop201521

       

      What I hv tried:

      1. Left join all the data source, but the "TotalPrice" from Sale and Sale2 would become two different fields, while the "Date" can't be synchronized too.

      2. Create a calculated field applying both "TotalPrice" from Sale and Sale2, but Tableau shows that all the fields must be aggregate, and the "Date" can't be synchronized.

      3. Try to union both Sale & Sale2 when doing the data connection and using the Custom SQL, but it fails while they are from different source type.

       

      So, any alternative?

       

      Thanks so much

      Jimmy