Sorting Table Columns Based on Fiscal Year
Forums
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
Dear Brian,
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]]
Hi talebi, here is some code,
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
What if you add another
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.
It is hard to comment without
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.