2 Replies Latest reply on May 29, 2017 5:24 PM by Bharadwaj Munjuluri

    Generate Report from two differently structured data sources

    Bharadwaj Munjuluri

      I have 2 tables of transaction data each in different format (column names, values, etc).

      For eg:

      Table A has:

      TransID, AMOUNT,CURRENCY,TRANS_DATE,TRANS_TYPE

      with values like 1,$100,USD,28-Mar-2017,PAYMENT

       

      Table B has:

      RefNo,EXCHANGE_RATE,AMOUNT1,AMOUNT2,AMOUNT3, TRANS_TYPE,TRANS_SUBTYPE,CCY,TRANS_DATE

      with values like 1,1.56,$45,$9.45,$34,COLLECTION,CHEQUE,SGD,28-Mar-2017

       

      Data in both these tables is not consistent i.e,

      I can not be sure that:

      1. Currency SGD if it is there in Table A, will be there in Table B.

      2. No of rows in Table A has more unique values than Table B.

       

      I know that the date would remain same in both transaction tables.

       

      Requirement

      I need to create a report to get the aggregates from these two tables. i.e, I need a table/chart in one Tableau sheet which has someting like below

      USDSGDOTH
      Collection [From Table B]$$$$$$$$$
      Payment [From Table A]$$$$$$$$$
      Total$$$+$$$$$$+$$$$$$+$$$

       

      I am unable to attach a sample twbx package file - sorry for that.

       

      Please help!

        • 1. Re: Generate Report from two differently structured data sources
          Ken Flerlage

          Can you provide a masked data set for us to look at? It's hard to understand what your issue is from your explanation. If not, can you clarify the following:

           

          Which fields show USD, SGD, OTH, etc.?

          Is it possible that Table A has records in it without a match to Table B? How about vice versa?

          What do you mean by "No of rows in Table A has more unique values than Table B."?

          • 2. Re: Generate Report from two differently structured data sources
            Bharadwaj Munjuluri

            Please see below:

            TABLE A

            TransID

            AMOUNT

            CURRENCY

            TRANS_DATE

            TRANS_TYPE

            1

            $43

            USD

            28-Mar-2017

            PAYMENT

            3

            $34

            SGD

            28-Mar-2017

            COLLECTION

            4

            $56

            USD

            28-Mar-2017

            PAYMENT

            8

            $59

            SGD

            28-Mar-2017

            PAYMENT

            TABLE B

            RefNo

            EXCHANGE_RATE

            AMOUNT1

            AMOUNT2

            AMOUNT3

            TRANS_TYPE

            TRANS_SUBTYPE

            CCY

            TRANS_DATE

            10

            1.45

            43.5

            40

            30.5

            COLLECTION

            CHEQUE

            THB

            28-MAR-2017

            30

            1.45

            50.4

            30

            20

            PAYMENT

            CASH

            THB

            28-MAR-2017

            40

            1.45

            30.2

            12

            3

            COLLECTION

            DD

            THB

            28-MAR-2017

            80

            20

            100

            20.4

            30

            COLLECTION

            CASH

            INR

            28-MAR-2017

             

            "No of rows in Table A has more unique values than Table B." - I read that for choosing a primary data source, I choose a table with more granularity. However, as these are two separate Transaction tables, I can not be sure which one would be more granular compared to the other.

            Which table in the above example should I consider Primary?

            Secondly, how to show aggregates of all these in one report?