IronPython: Get Unique values from Data Column

-

IronPython Script to extract the unique values from a Data Tables' Column.


#-------Gets Unique values from a Column---------#
def fnGetUniqueColVal(TblName,ColName):
       #Get Table and Column References
       dtRef=Document.Data.Tables[TblName]
       ColCursor=DataValueCursor.CreateFormatted(dtRef.Columns[ColName])      
       fnUnique = ""
       fnUniquearr = {}
       for  row in  dtRef.GetRows(ColCursor):
              rowIndex = row.Index
              fnUniquearr[ColCursor.CurrentValue] = 1
       for key, value in fnUniquearr.items() :
              fnUnique = fnUnique + key + ","
       # Return either a String or the Unique list
       return fnUnique[:-1]
       #return fnUniquearr

def fnGetUniqueFiltColVal(TblName,ColName,FiltScheme):
       #Get Table and Column References
       dtRef=Document.Data.Tables[TblName]
       ColCursor=DataValueCursor.CreateFormatted(dtRef.Columns[ColName])
       filtRows = Document.Data.Filterings[FiltScheme].GetSelection(dtRef).AsIndexSet()      
       fnUnique = ""
       fnUniquearr = {}
       for  row in  dtRef.GetRows(filtRows,ColCursor):
              rowIndex = row.Index
              fnUniquearr[ColCursor.CurrentValue] = 1
       for key, value in fnUniquearr.items() :
              fnUnique = fnUnique + key + ","
       # Return either a String or the Unique list
       return fnUnique[:-1]
       #return fnUniquearr
###----------------------------------------------------------------------------###

How to use the Function


#print fnGetUniqueColVal(TableName,ColumnaName)
print fnGetUniqueColVal("SalesAndMarketing","State")

#print fnGetUniqueFiltColVal(TableName,ColumnaName,FilteringScheme)
print fnGetUniqueFiltColVal("SalesAndMarketing","State","FilteredRows")






** As mentioned previously, most of the scripts we provide on our website will be developed as functions, which will enable us to both reuse and build up a library as we go along.**

Comments

Post a Comment

Popular posts from this blog

IronPython: Mark Rows, which are currently Filtered

-
When using the Data-On-Demand, there is a need, where you need to drive the on-demand data based on current selection. Below is helpful as you do not need to make your users Mark all Rows manually before proceeding to next steps. #=================================================# from Spotfire.Dxp.Data import * #Setup your Source Table below where the Filtered rows need to be captured tblName= "SalesAndMarketing" FiltScheme = "FilteredRows" MarkScheme = "Marking" #Gets Source Table reference dtRef=Document.Data.Tables[tblName] #Gets the Current Filtered Rows as per the filtering schema filtRows = Document.Data.Filterings[FiltScheme].GetSelection(dtRef).AsIndexSet() #Marks the rows as per the filtered Rows Document.Data.Markings[MarkScheme].SetSelection(RowSelection(filtRows), dtRef) #Do your table refreshes here Document.Data.Tables[ "Table1 to Refresh" ].refresh() Document.Data.Tables[ "Table2 to R...
Read more

Dynamic Page Layout setup

-
Image
Sometime Spotfire layout is too crammed to see the chart content legibly and it helps to see the page in a different perspective. This can be achieved with the help of Bookmarks and Action Buttons Step 1: Capture bookmarks of different Screen Layouts that you want to present to user. You can resize charts to highlights the ones you want to stand out. ** Do not delete charts, as bookmarks will does not recreate it. Only Resize the layouts Step 2:  In a Text area, Create Action Buttons/Link/Image and tie them to the corresponding Bookmarks-"Page Layout and Visualization" element. ** Remember Bookmarks capture a lot more information (Filters, Marking etc..), we only want to apply the Layout settings. Step 3: That's it, you should be ready, test clicking on various buttons and see the screen layout change, giving importance to different charts as configured.
Read more