The Spotfire Community is moving to TIBCOmmunity and this forum location has closed. During the transition, you can still search the old forums but posting has been disabled. We encourage you to pick up the discussion at the new Spotfire community on TIBCOmmunity.
Showing Distinct Rows in your Data Table - Tip of the Week

Tip of the Week

Showing Distinct Rows in your Data Table

One of the most commonly asked questions I get in training is how to show distinct rows inside a Data Table.  People need the full set of rows for most of the analysis, but may also want to be able to display a table with just distinct values.

Assume you have a simple data set like the following:

And in at least one location, you want to show only distinct rows, as shown below:

What you need to do is create a new Data Table , which takes your original Data Table as the source and then pivots it.

To start, you goto File > Add Data Table.

 

 

You will then choose the source type to be ‘Existing Data Table in my Analysis’ (shown as Step 1 in the figure above), then if you have more than one Data Table loaded, make sure you select the right Data Table (shown in Step 2 in the figure above), and then select the  ‘Pivot’ Transformation and click ‘Add…’ (shown in Step 3 in the figure above). The Pivot Data dialog will appear.

 

The ‘Row identifier’ should be the first column in the data set, (or the key column).

The ‘Column titles’ and ‘Values and aggregation methods’ should both be ‘None’.

The ‘Transfer columns and aggregation methods’ should list all the remaining columns in the original Data Table, with the expression set to UniqueConcatentate for all columns.

You can then set the ‘Transfer column naming pattern’ to  %T (which just references the Column name and no aggregation method).

What this does is not really pivot the data, rather we use the pivot tool to transfer over all the columns in our original Data Table, but we apply a UniqueConcatenate aggregation to all the transfer columns. This will create the distinct effect we are looking for.

 
When you are done, you can click ‘OK’ to close the Pivot Data dialog and then ‘Finish’ to close the Add Data Table dialog.

When you are done, you will see a new data table added that has just distinct row.

You can also add a Data Relation so that the two Data Tables are related. This will allow users to mark rows in one of the data tables , and also mark related rows in the second Data Table. This is shown below where the user marked a row in the ‘Distinct’ Data Table, and then the related rows were also marked in the ‘Original Data Table’


Published Dec 06 2009, 08:57 PM by Kevin Hanegan
Filed under: ,

Comments

 

hvmarck said:

Whenever I need to show distinct rows only I simply use a Cross Table, using all the columns on the vertical axis, leaving the Horizontal Axes and the Cell values empty. For this data set this results in exactly the same thing.

In case a column in your data set contains different values for a given value in the key column, e.g. if your data looks like this

1 a c d

1 a c e

2 b f e

3 c r t

4 a f g

this can be done with a custom expression for the Vertical Axis of the Cross Table:

<[Column 1] NEST UniqueConcatenate([Column 2]) OVER ([Column 1]) as [Column 2] NEST UniqueConcatenate([Column 3]) OVER ([Column 1]) as [Column 3] NEST UniqueConcatenate([Column 4]) OVER ([Column 1]) as [Column 4]>

December 8, 2009 7:16 AM
 

Kevin Hanegan said:

Thanks for the comment. You are spot on, we even have an upcoming related tip that discussed the cross table approach. The only difference is some customer may want to use the raw data from the newly created data table in this approach in other visualizations or other ways than just the cross table.  However, if you just want to display it in a table, the cross table approach , as you said, is the same....great lead in for the future tips, as well, so we appreciate it:)

Kevin

December 8, 2009 10:50 AM
 

Showing Distinct Rows in your Data Table - Tip of the Week Get Pivot said:

Pingback from  Showing Distinct Rows in your Data Table - Tip of the Week Get Pivot

December 8, 2009 11:52 PM
 

Christof said:

Hi,

if you want to find out how often a column is in the dataset and still get them unique you can do the following:

Concatenate all columns into one (column name: [key]):

concatenate([Number2],[Number1],[Text2],[Text1])

Then count the keys "per" key (column name: [Duplicates]:

count([key]) OVER ([key])

Find the first line for each [key]:

if((rank(baserowid(),"asc",[key]))=1,"unique","duplicates")

You will get a new filter for to filter out duplicates.

Kind regards,

Christof

December 15, 2009 4:26 PM
 

Changing your Habits to Build Muscle | Bodybuilding Fitness Wisdom said:

Pingback from  Changing your Habits to Build Muscle | Bodybuilding Fitness Wisdom

December 18, 2009 2:25 AM
 

uggkensington said:

http://www.uggkensingtons.org/

http://www.uggkensingtons.org/ugg-fox-fur-short-boots-5531-c-54.html | UGG Fox Fur Short Boots 5531

http://www.uggkensingtons.org/ugg-kensington-boots-5678-c-2.html | UGG Kensington Boots 5678

http://www.uggkensingtons.org/ugg-rainier-eskimo-boots-5189-c-85.html | UGG Rainier Eskimo Boots 5189

http://www.uggkensingtons.org/ugg-retro-cargo-boots-1895-c-3.html | UGG Retro Cargo Boots 1895

December 27, 2011 8:17 PM
 

uggkensington said:

http://www.uggkensingtons.org/

http://www.uggkensingtons.org/ugg-fox-fur-short-boots-5531-c-54.html | UGG Fox Fur Short Boots 5531

http://www.uggkensingtons.org/ugg-kensington-boots-5678-c-2.html | UGG Kensington Boots 5678

http://www.uggkensingtons.org/ugg-rainier-eskimo-boots-5189-c-85.html | UGG Rainier Eskimo Boots 5189

http://www.uggkensingtons.org/ugg-retro-cargo-boots-1895-c-3.html | UGG Retro Cargo Boots 1895

December 27, 2011 8:17 PM
 

uggskensingtonsale said:

www.uggskensingtonsale.org

www.uggskensingtonsale.orgugg-adirondack-boots-ii-c-6.html | UGG Adirondack Boots II

www.uggskensingtonsale.orgugg-adirondack-tall-boots-c-11.html | UGG Adirondack Tall Boots

www.uggskensingtonsale.orgugg-amberlee-boots-c-34.html | UGG Amberlee Boots

www.uggskensingtonsale.orgugg-annabelle-boots-c-35.html |

December 28, 2011 4:31 AM
 

uggretrocargosale said:

January 4, 2012 8:52 PM
 

Yogesh said:

How can i merge two different text files containing similar informations from my desktop. Like Union type join in sql.

April 26, 2013 6:33 AM
 

Treat said:

I enjoy your composing design, do carry on creating! I will be back!

June 24, 2013 9:22 AM
 

Bass said:

Horoscope astrocenter qui est mon ange gardien gratuit

July 7, 2013 1:24 AM
 

Contreras said:

Horoscope homme sagittaire tirage de cartes gratuits en ligne

July 13, 2013 7:43 AM
 

Mayo said:

Taureau horoscope signe astrologique taureau femme

July 13, 2013 8:16 AM
 

Richardson said:

Formation referencement 512banque formation a

domicile onglerie

July 14, 2013 12:52 AM
 

Swank said:

Formater disque dur windows vista formation professionnelle continue

definition

July 14, 2013 6:20 AM
 

Elliot said:

Logiciel astrologie chinoise tirage de carte marie claire

July 14, 2013 11:58 AM
 

Boren said:

Format polaroid centre de formation permis c

July 14, 2013 11:38 PM
 

Poindexter said:

Tirage de tarot gratuit persan cancer astrologie wikipedia

July 17, 2013 1:45 PM
 

Pickard said:

Rtl horoscope du jour les quatres verites

July 18, 2013 9:28 PM
 

Arnold said:

Lhoroscope.com livre astrologie chinoise

July 19, 2013 4:33 PM
 

Milliken said:

Oh my goodness! a wonderful post dude. Appreciate it However My business is experiencing issue with ur rss .

Don’t know why Unable to enroll in it. Perhaps there is any individual finding the identical rss problem?

Anyone who knows kindly respond. Thnkx

July 22, 2013 12:11 AM
 

Stanton said:

My neighbor and I had been just debating this specific topic, he is

in most cases looking to prove me incorrect. Your view on this is outstanding

and exactly how I truly think. I just now mailed him this blog

to show him your point of view. After searching over your website

I bookmarked and will be coming back to discover your new posts!

July 25, 2013 1:46 AM
 

Kuykendall said:

You just hold to shuffling them come to find out if they experience their other, deeper motivations are equaling

met. That lets him to have got fun. How you deal yourself when she is his mother, but

constantly conceives a man is all done with a kinship, the male person suits afraid of losing him forever and a day.

google sniper reviews

July 30, 2013 10:54 AM
 

Grossman said:

The raid should never be close to the old world,

the new legislation would ban the transfer of precious metals wow gold to Iran, including gold.

Its a great way to get that cup back. Occupations may

help hone abilities as well as golf enthusiasts like Justin Timberlake who owns his own course and actor Will Smith.

Don't get me wrong, I LOVE my wedding set, but he's the one who wants to wait 24 hours for delicious

cinnamon rolls?

August 2, 2013 3:39 PM
 

Morrill said:

There is noticeably big dough to comprehend this.

I assume you’ve made specific nice points in features also.

August 3, 2013 3:09 PM
 

Yocum said:

If your back is sore just thinking about it too

much take you out of the van as I sat made me suddenly crave a cold drink.

Humungous variety of motorbikes, this property has been put to good use.

motorbikes, on the $1 billion maturity in 2009, then the victim can claim for the some of the more specialized motorcycles actually increase in value.

August 6, 2013 11:38 AM
 

High said:

Femme actuelle voyance gratuite immediate par telephone

August 7, 2013 3:36 PM
 

Castleberry said:

Can I just say what a aid to look for someone who actually knows what theyre speaking

about via internet. You definitely identify that the path to carry a difficulty to

mild and make it important. More people need to read this and perceive this aspect of the story.

I cant imagine youre not more fashionable because you

positively have the gift.

August 7, 2013 10:19 PM
 

Atkins said:

Rune tirage gratuit horoscope cancer 2011 gratuit

August 9, 2013 9:09 PM
 

Conger said:

Great write-up. I am a normal visitor of your web site

and appreciate you taking the time to maintain the fine site.

I will be a frequent visitor for a long time.

August 9, 2013 11:33 PM
 

Pannell said:

Horoscope elle 2011 les horoscopes 2011

August 10, 2013 3:57 AM
 

Thorton said:

Haas horoscope demain horoscope metro

August 10, 2013 8:27 AM
 

Vang said:

very good post, i surely enjoy this fabulous website, persist in it

August 10, 2013 2:08 PM
 

Smyth said:

You FINALLY want that the respect off your

family and friends?

August 11, 2013 4:22 AM
 

Moran said:

Because I website possessor I believe that the subject material here is rattling fantastic , appreciate it for the efforts.

August 11, 2013 5:34 AM
 

Talbot said:

Thanks, I have recently been looking for details about this topic for ages and yours

is that the best I have located so far.

August 11, 2013 11:46 AM
 

Low said:

Thank you for one more informative website. Where else

could I find out that type of information written in such a perfect system?

I have a project that I’m just now working on, and I’ve been on the

look out for such info.

August 11, 2013 9:49 PM
 

Arevalo said:

A individual necessarily lend a hand to make critically posts I might state.

Which is that the first time I frequented your website page and so far?

I amazed with the analysis you made to create

this actual put up extraordinary. Great process!

August 13, 2013 8:15 AM
 

Evans said:

Rtl.fr astro connaitre mon avenir gratuitement

August 16, 2013 5:13 AM
 

Wells said:

Horoscope lion avril horoscope chinois par annee

August 16, 2013 7:16 PM
 

Roman said:

Hi. I wanted to thank you for that the great details

youve posted on your internet site. I will definitelycome back to check it out

again and have subscribedto your RSS feed.

Have an good day.

August 18, 2013 2:58 AM
 

Harvey said:

Voyance gratuite en direct horoscope chinois 1981

August 19, 2013 6:22 PM
 

Swope said:

Horoscope haas 2012 horoscope verseau femme

August 20, 2013 10:06 AM
 

Stover said:

Astrologie siderale horoscope gemeaux semaine

August 20, 2013 12:53 PM
 

Brent said:

Mature amateur nue theme psp porno

August 24, 2013 3:47 AM
 

Freeland said:

Jeu de belline titties signe astrologique chinois signification

August 26, 2013 3:43 AM
 

Boucher said:

Astro mariage gratuit voyance par le pendule

August 28, 2013 5:00 PM
 

Torrence said:

Tirage de l oracle de belline gratuit playmate signe astrologique chinois 1993

August 30, 2013 10:17 PM
 

Hollis said:

L essentiel.lu horoscope horoscope mois juin

September 8, 2013 2:04 PM
 

Smythe said:

Astrologie couple gratuit free daily horoscope

September 18, 2013 6:42 PM
 

Matias said:

Photos cul amateurs beurrettes rebelles

September 19, 2013 5:18 AM
 

Dymock said:

New porn film video porno telechargement gratuit

September 20, 2013 5:20 PM
 

Earnshaw said:

logiciel de dessin gratuit en francais logiciel bibliotheque gratuit

September 21, 2013 8:18 AM
 

Annand said:

Pornographie xxx sexe hd streaming

September 22, 2013 11:13 PM
 

Darr said:

Sex toys discount bon gros culs

September 23, 2013 8:06 AM
 

Barrallier said:

Horoscope tarot 2012 formation cartomancie

September 25, 2013 7:10 AM
 

Wroblewski said:

I goo to see every day some web sites and information ites to read content, except this blog offers quality

based articles.

September 26, 2013 5:05 PM
 

Franks said:

devis de cuisine gratuit devis fenetre sur mesure

September 29, 2013 10:48 AM
 

Brumfield said:

Temps d ejaculation rencontre sexe lorient

September 30, 2013 10:11 PM
 

Macaulay said:

lecturas del tarot tarot del amor gratis verdadero

October 2, 2013 7:05 PM
 

Symonds said:

The approval process can be very fast to ensure you happen to be able to acquire

the advance loan or payday loans easily and quickly super real if

you reduce your emi, the alternative happens, your loan tenure increases.

October 4, 2013 9:22 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

Syndication

Other Spotfire Blogs

©Copyright 2000-2011 TIBCO Software Inc | Privacy Policy | Terms of Use I Blog I Contact Us I Content Center