Skip to main content

Sorting Table Columns Based on Fiscal Year

Hi Brian,
I hope this message finds you well. I am reaching out to seek assistance in sorting the columns of my table based on our fiscal year, which runs from April to March. The table may include only a subset of months, for example, from April to August.
I am wondering if there is a more efficient way to achieve this rather than manually sorting the columns. I attempted to use an "if" function, but I encountered difficulties in implementing it successfully.
Ideally, I would like to extract the data with logic similar to the following:
 
 
' Data: Direct Extraction
Function DirectExtraction
Set db = Client.OpenDatabase("Balancing Reports\AP_BS_Monthly_BR_2023_24_up_to_December.IMD")
Set task = db.Extraction
if "Apr_23" in fields:
    task.AddFieldToInc("APR_23")
elif "May_23" in fields:
    task.AddFieldToInc("May_23")
and so on.
dbName = "Balancing Reports\EXTRACTION3.IMD"
task.AddExtraction dbName, "", ""
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
What could be instead on "in" and "fields"?
Thank you

Brian Element Fri, 02/02/2024 - 12:15

It is hard to comment without seeing the file strucutre.  Another alternative instead of doing the extract and reording the columns is to use a view, you would have to create one first manually and then you could use the client.openview() to use that view.  The downside to views is it needs the file to always have the same file structure so if your files are different column names and such then a view would give an error.

If you can't use view then it is doing the extraction and reording the fields like you are currently doing.  

talebi Fri, 02/02/2024 - 12:55

Dear Brian,
I appreciate your assistance. Could you please help me with coding the 'if' function in the above code? I'm looking to inquire whether the X column is present in all columns. In Python, I usually use the following approach:
if X in table.columns: table = table[table[X]]
 

talebi Wed, 02/07/2024 - 11:26

In reply to by Brian Element

Hi Brian. I am looking for IDEAScript not Python. I just gave you an example of Python code. I wanted to know if IDEA has something like Python's df.columns function or not.Thanks,
 

Brian Element Wed, 02/07/2024 - 15:17

Hi talebi, here is some code, it uses what you started but I changed the file and fields for a file in the sample folder.  I added a function to check if a field exists and updated the if statements.  Let me know if you have any questions.


Option Explicit
Dim sFilename As String

Sub Main
	sFilename = "Sample-Detailed Sales.IMD"
	Call DirectExtraction()
End Sub

Function DirectExtraction
	Dim db As database
	Dim task As task
	Dim dbName As String
	
	Set db = Client.OpenDatabase(sFilename)
	Set task = db.Extraction
	If check_if_field_exists("SALESREP_NO") Then 'field exists
		task.AddFieldToInc("SALESREP_NO")
	End If
	If check_if_field_exists("DOES_NOT_EXIST") Then
		task.AddFieldToInc("DOES_NOT_EXIST")
	End If
	
	If check_if_field_exists("PROD_CODE") Then
		task.AddFieldToInc("PROD_CODE")
	End If
	dbName = "Field Test.IMD"
	task.AddExtraction dbName, "", ""
	task.PerformTask 1, db.Count
	Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
End Function

Function check_if_field_exists(fieldname) As Boolean
	Dim db As database
	Dim table As table
	Dim field As field

	Set db = Client.OpenDatabase(sFilename)
		Set table = db.TableDef
			err.number = 0 'reset in case of a previous error
			On Error Resume Next 'If the field does not exist it will give an error but this line lets the code continue
			Set field = table.GetField(fieldname)
				If err.number <> 0 Then
					check_if_field_exists = False
					err.number = 0
				Else
					check_if_field_exists = True
				End If	
			Set field = Nothing
		Set table = Nothing
	Set db = Nothing
End Function

djs Fri, 02/16/2024 - 09:24

What if you add another calculated fiscal period field?  Using nested if and string manipulation statements convert the text fields to numeric fields :
Apr_23 to 1.23
May_23 to 2.23
Jun_23 to 3.23
Etc.....
You can then sort the periods by where they actually fit in the fiscal calendar vs alphabetically.  You could then perform your extracts using the range of periods.
Unless maybe I missed something in the original request.