3 Replies Latest reply on May 11, 2013 6:49 AM by Antonio Willybiro

    How best should i blend this data/

    Antonio Willybiro

      Hi there -

       

      i have multiple data sources, 12 to be precise. One for each month.

      the data structure is exclatly the same i.e same dimension and measures. it's just that the period (date e.g. 2013.05 ... 2013.08) and the values change.

       

      what i previously did was to connect the first data source then went to Extract and 'Add data form file' in order to add the other months and easily report on the full data set.

      the issue i have with this method is that occasionnaly i may have to update a specific month (data source), replcaing all existing data with updated once (structure is still the same). if i use the existing method it means i have to recreate the full data set everymonth instead of just refreshing the one month data set that was changed.

       

      How best should i blend this data? same structure each month just new period and new set of values for some of the columns.

       

      thanks

      Antonio

        • 1. Re: How best should i blend this data/
          Prashant Sharma

          Hi Antonio,

           

          If you want to take this as multiple dataset then only take those columns which are having different values in different tables and those columns which you are going to change at the tableau level. It is better to not include those fields which are common again in different datasets.

           

          Thanks

          1 of 1 people found this helpful
          • 2. Re: How best should i blend this data/
            Shawn Wallwork

            Antonio, since your structure is exactly the same for each one, you can use a Custom SQL statement. When you connect to the first data source and click Custom SQL, T generates the first statement. Open up the large editor [...] and delete everything from between the SELECT and the FROM. Now type in something like this:

             

            SELECT *,

            "Jan." AS [TYPE]

            FROM [JanData$]

             

            Click Preview to make sure you got the syntax right. Now add all the other data sources like this:

             

            SELECT *,

            "Jan." AS [TYPE]

            FROM [JanData$]

            UNION

             

            SELECT *,

            "Feb." AS [TYPE]

            FROM [FebData$]

            UNION

             

            (etc.)

             

            By using the '*' wildcard you're able to eliminate all individual declarations of the fields. After you do the connection then you can pull the extract. When you need to make changes, take the extract off, refresh the connection, then do another extract. You won't have to re-created any tables or rewrite any SQL code. And by using the wildcard, you won't have to change the SQL even if you add new fields (columns) to your data sources.

             

            The only rule to remember is the field have to be the same in all the data sources.

             

            Good Luck.

             

            --Shawn

            • 3. Re: How best should i blend this data/
              Antonio Willybiro

              Thank you guys.

              i'll give these a try