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)

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

      Sale:(in MS SQL)

      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)

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


      Ideal Result:

      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