-
1. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Yuriy FalOct 3, 2015 8:53 PM (in response to Dom Armano)
Hi Dom,
If your data sources are relational ones,
you'd be better to create a CatalogName field
in each of your data sources via Custom SQL.
There are two ways of doing it:
1) From the Datasource UI choose:
Data --> Convert to Custom SQL
Then edit the first line of your SQL statement like this:
SELECT 'BITS' as CustomerName,
<your code follows>
2) Re-create your data source schema from scratch.
When placing the first (fact) table / view on a pane, choose:
Data --> Convert to Custom SQL and edit your code as above.
Then join other tables / views / Custom SQL selects.
Hope this makes sense.
Yours,
Yuri
-
2. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
kettan Oct 15, 2015 3:10 AM (in response to Yuriy Fal)Data --> Convert to Custom SQL and edit your code as above.
Nice tip. Strangely, I cannot remember seeing it before. I guess it is either a relatively new feature or forgotten because it is so long time since I used Tableau. This is the only official writing about it which I have been able to find until now: Converting a Connection to Custom SQL | Tableau Software
That said, there are several other answers/articles that mention Convert to Custom SQL:
Re: Hello, in version 8.2, where is the custom sql option in the excel workbook connection ?
Re: 8.2 New Connection has Window Less Functionality?
Self Blends and Data Padding - The Information Lab
Tableau Doctor: 1 Column, 2 Dates? Use Custom SQL | Tableau Software
https://www.google.com/search?q=%22Convert+to+Custom+SQL%22+site%3Atableau.com
-
3. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Oct 30, 2015 11:57 AM (in response to Yuriy Fal)Yuriy,
This is an interesting feature I didn't know.
Still this won't help him because this doesn't allow union from multiple data sources.
Scaffolding is a way to help him to some extent.
Vizible Difference: Blending Data Via Multi-Dimensional Scaffolding
-
4. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Yuriy FalOct 30, 2015 1:33 PM (in response to Alexander Mou)
Hi, Alexander,
If I understood correctly, Dom has been trying
to append an extract based on a "first DS" (BITS_Extract.tde)
with data from an extract based on a "second DS" (CS_Extract.tde) --
the same as described in Tableau Desktop Online Help:
http://onlinehelp.tableau.com/current/pro/online/mac/en-us/help.htm#extracting_addfromfile.html
Doing just that is not a UNION per se,
it is simply adding a next portion of data,
so the CatalogName field should be already
materialized in each extract before appending.
The latter is not possible
with a row-level single-value "calculation".
Correct me if I'm wrong.
Yours,
Yuri
-
5. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Oct 30, 2015 2:06 PM (in response to Yuriy Fal)I see. Thanks!
This will enable union across multiple data sources, it seems.
-
6. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Yuriy FalOct 30, 2015 2:23 PM (in response to Alexander Mou)
Thinking of it a little bit more,
there are cases when it would be better
to preserve some properties of datasources
when doing "light" transformations in Tableau --
such as having Row_ID for a Pivot,
or Datasource_ID for an upcoming 9.2 Union.
Just my 0.02$
-
7. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Oct 30, 2015 8:21 PM (in response to Yuriy Fal)Yuri,
It seems that such appended data is a one-time thing. It can't be made dynamic. That means, if I added data from Source A to Extract AAA, then refreshing AAA will not add data from Source A again.
So the append is materialized. It is not a union on the fly.
-
8. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Oct 30, 2015 8:24 PM (in response to Alexander Mou)If the extract AAA is an extract from a database, after appending data from a file or a data source, it can keep adding data by incremental refresh from the original database.
-
9. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Yuriy FalNov 1, 2015 3:14 AM (in response to Alexander Mou)
Yep, append from file/extract
follows the same logic
as an incremental refresh.
Doing full refresh removes
all appends / increments.
-
10. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Oct 31, 2015 3:33 PM (in response to Yuriy Fal)Yuri,
I found that Convert To SQL doesn't apply to extracts from Excel files.
Do you know why?
Maybe that there is already an option to do that via legacy connection.
-
11. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Yuriy FalNov 1, 2015 4:01 AM (in response to Alexander Mou)
1 of 1 people found this helpfulAlexander,
It is my understanding that Tableau exposes Custom SQL interface
for each database engine that is able to respond to SQL queries --
be it ANSI SQL or the likes (bQL for Google BigQuery or HiveQL for Hive).
The Legacy Connector for Text / Excel uses plain old
MS Jet Database Engine, which talks plain SQL, too.
That's why one could use a Custom SQL interface,
hence Convert to Custom SQL.
"Native" Text / Excel connector -- new as for the version 8.2,
and in particular with the introduction of Tableau for Mac -- talks no SQL.
Hence no Custom SQL / Covert to Custom SQL.
(And no non-equi-joins :-)
I am thinking of "native" connector as an advanced (and portable)
parser / loader for Tableau Data Engine. I am speculating further
that common core is shared among other connectors as well --
R/SAS/SPSS - Google Analytics - Web Data Connector.
Yours,
Yuri
-
12. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Nov 1, 2015 7:30 PM (in response to Yuriy Fal)Yuri,
This gentleman can apply "convert to custom sql" to excel file.
Self Blends and Data Padding - The Information Lab
I can't. I am using 9.1. and the option is grayed when the file is excel.
See if you can reproduce it?
His screenshot:
My screenshot:
-
13. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Yuriy FalNov 1, 2015 11:46 PM (in response to Alexander Mou)
Hi Alexander,
Citing this gentleman's blog post (one paragraph above the picture):
For this example with Superstore Sales, the mention of Custom SQL comes with a caveat, and that is that I’m going to have to use the Legacy Excel connector to take advantage of JET, and as a result I’m afraid this is going to be PC only. If you’re on MAC and have data in Excel you’re going to have to do some pre-processing to either get it into a database or utilise a product like Alteryx.
To recap the legacy connecter, the trick is to select the appropriate file “open” option when you locate your Excel file:
So the pre-requisites are:
-- Windows
-- Tableau Desktop
-- MS Jet Database Driver (all versions or all versions)
-- When opening Excel / CSV file, choose Open with Legacy Connection
Yours,
Yuri
-
14. Re: I am trying to combine multiple extracts, but need to keep each extract identifiable
Alexander Mou Nov 1, 2015 11:51 PM (in response to Yuriy Fal)Yuri, I did see that passage.
But it seems his screenshot shows he is using Data>Convert to Custom SQL to
implement his SQL code.
Could you reproduce that with an excel file?
On Sun, Nov 1, 2015 at 11:46 PM, Yuriy Fal <