-
1. Re: Aggregating strange dataset
Matt LuttonSep 30, 2013 7:21 AM (in response to CJ Ragnarsson)
Can you post an example data set in a packaged workbook (.TWBX) file? That will help us help you.
-
2. Re: Aggregating strange dataset
Jim Wahl Sep 30, 2013 7:32 AM (in response to CJ Ragnarsson)What database are you using? You're probably better off doing this with custom SQL, since you need to both split the rows and unpivot them. I.e., you want the final data to look something like:
Name Category Rank Amount Albert Books 1 10 Albert PC 2 8 Albert Home 3 7 Bob PC 1 20 Bob Electronics 2 8 Bob Books 3 6 I'm sure it's possible to do this in Microsoft Jet (the database connection used for text, Excel and Access data sources), but it might be ugly and slow with a bunch of string manipulation. On the other hand it's a straight forward Python script (or any other script tool, including VBA (?)). And if you're using a proper database, you might have a more efficient split function.
Jim
-
3. Re: Re: Aggregating strange dataset
Jim Wahl Oct 1, 2013 12:19 AM (in response to Jim Wahl)Another quick thought. If your your example data above is your data file, then you could add a header row, connect as text and use a simple custom SQL connection to reshape the data.
Data Source Text File:
Albert, Books, 10, PC, 8, Home, 7 Bob, PC, 20, Electronics, 8, Books, 6
Add Header Row:
Customer, Cat1, Amt1, Cat2, Amt2, Cat3, Amt3 Albert, Books, 10, PC, 8, Home, 7 Bob, PC, 20, Electronics, 8, Books, 6
You can append a header row in Windows with:
echo Customer, Cat1, Amt1, Cat2, Amt2, Cat3, Amt3 > data.txt type OriginalDataFile.txt >> data.txt
If you're using linux, just replace type with cat.
Connect to text data source in Tableau and select Custom SQL:
SELECT [data#txt].[Customer] AS [Customer], "1" as [Rank], [data#txt].[Cat1] AS [Category], [data#txt].[Amt1] AS [Amount] FROM [data#txt] UNION ALL SELECT [data#txt].[Customer] AS [Customer], "2" as [Rank], [data#txt].[Cat2] AS [Category], [data#txt].[Amt2] AS [Amount] FROM [data#txt] UNION ALL SELECT [data#txt].[Customer] AS [Customer], "3" as [Rank], [data#txt].[Cat3] AS [Category], [data#txt].[Amt3] AS [Amount] FROM [data#txt]
The data now looks like
Category Customer Rank Amount Books Albert 1 10 PC Bob 1 20 PC Albert 2 8 Electronics Bob 2 8 Home Albert 3 7 Books Bob 3 6 And you can get your result easily in Tableau:
Jim