Equation Editor vs Custom Function

Summary

In diesem Video zeigt der Sprecher zwei Methoden, wie man ein unformatiertes Datumsfeld in ein nutzbares Format umwandeln kann. Die erste Methode verwendet den Gleichungseditor, um das Datum mit einer komplexen Gleichung zu reformatieren. Die zweite Methode nutzt eine benutzerdefinierte Funktion, die entweder in der Idee-Skriptsprache oder Python geschrieben werden kann. Der Vorteil der benutzerdefinierten Funktion liegt in ihrer Lesbarkeit und Flexibilität, da sie auch für komplexere Aufgaben eingesetzt werden kann. Der Sprecher erklärt beide Methoden detailliert und zeigt, wie sie in der Datenbank implementiert werden.

Takeaways

• 📅 Das Video zeigt zwei Methoden, um ein unformatiertes Datumsfeld in ein nutzbares Format zu konvertieren.
• 🔢 Die erste Methode verwendet den Gleichungseditor, um das Datumsfeld umzuformatieren.
• 📝 Die Umformatierung erfolgt durch die Verwendung der Funktion 'character to date' und erfordert das Hinzufügen von Trennzeichen.
• 🔄 Die Umformatierungslogik ist in der Gleichung mehrfach wiederholt, was zu einer langen und manchmal schwer lesbaren Gleichung führt.
• 🎨 Die zweite Methode ist die Verwendung einer benutzerdefinierten Funktion, die entweder in ID-Script oder Python geschrieben werden kann.
• 📚 Benutzerdefinierte Funktionen ermöglichen es, direkt mit der Datenbank über virtuelle Felder zu interagieren.
• 🔍 In ID-Script sind die meisten Funktionen des Gleichungseditors verfügbar, indem man das '@'-Symbol durch das 'I'-Symbol ersetzt.
• 📊 Die benutzerdefinierte Funktion kann mit mehr Komplexität und Lesbarkeit umgesetzt werden als die Gleichungseditormethode.
• 🔧 Die benutzerdefinierte Funktion kann auch Schleifen und Python-Befehle verwenden, was eine breitere Funktionalität ermöglicht.
• 📆 Am Ende der Umformatierung kann das umgeformte Datumsfeld mit der 'character to date'-Funktion in ein Datumsfeld konvertiert werden.
• 📈 Die benutzerdefinierte Funktion liefert das gleiche Ergebnis wie die Gleichungseditormethode, aber mit einer besseren Lesbarkeit und Flexibilität.

Q & A

Was ist das Ziel des in der Videobeschreibung erwähten Prozesses?

Das Ziel ist, ein Datumsfeld im nicht korrekten Format in ein für die Verwendung in IDEA geeignetes Format zu konvertieren.

Welche beiden Methoden werden im Video zur Formatierung des Datumsfelds verwendet?

Die beiden Methoden sind die Verwendung des Gleichungseditors und die Erstellung einer benutzerdefinierten Funktion.

Was ist der Vorteil der benutzerdefinierten Funktion im Vergleich zum Gleichungseditor?

Die benutzerdefinierte Funktion ist in der Regel lesbarer und ermöglicht es, komplexere Gleichungen und Schleifen zu verwenden. Zudem hat man mit der Möglichkeit, Python-Befehle zu verwenden, Zugriff auf eine breitere Funktionalität.

Wie wird die Funktion 'character to date' im Gleichungseditor verwendet?

Die Funktion 'character to date' wird dazu verwendet, um eine Zeichenfolge in ein Datum zu konvertieren, indem sie in das gewünschte Format (Tage-Monats-Jahres) umgestaltet wird.

Welche Schritte unterliegt die ursprüngliche Zeichenfolge im Gleichungseditor?

Die ursprüngliche Zeichenfolge wird in Teile aufgeteilt, um das Jahr, den Monat und den Tag zu identifizieren, und anschließend entsprechend formatiert, um das Datum korrekt darzustellen.

Wie wird die Länge der Zeichenfolge im Gleichungseditor überprüft?

Die Länge der Zeichenfolge wird überprüft, um zu bestimmen, ob das Jahr zwei oder vier Ziffern lang ist und ob der Monat eine Ziffer oder zwei Ziffern hat. Auf diese Weise wird die Zeichenfolge entsprechend formatiert.

Was ist die Funktion von 'I split' in der benutzerdefinierten Funktion?

Die Funktion 'I split' wird verwendet, um die ursprüngliche Datumszeichenfolge anhand von Trennzeichen (Hyphens) in ihre Bestandteile zu unterteilen.

Wie wird im Gleichungseditor die Konvertierung von Zeichen in ein Datum erreicht?

Die Konvertierung wird durch die Verwendung der 'character to date'-Funktion erreicht, die die formatierte Zeichenfolge in ein Datum umwandelt.

Welche Art von Funktionen sind in der benutzerdefinierten Funktion verfügbar?

In der benutzerdefinierten Funktion sind die meisten Funktionen aus der Gleichungseditor-Version bis zur Version 9 von IDEA verfügbar, einschließlich der 'I split'-Funktion.

Wie wird die benutzerdefinierte Funktion in der Datenbank implementiert?

Die benutzerdefinierte Funktion wird als virtueller Datumsfeld in der Datenbank implementiert, wobei sie mit der 'character to date'-Funktion in der Gleichungseditor verknüpft wird.

Welche Python-Befehle können in benutzerdefinierten Funktionen verwendet werden?

Mit der Möglichkeit, Python-benutzerdefinierte Funktionen zu erstellen, haben Sie Zugriff auf alle Python-Befehle, was eine breitere Funktionalität im Vergleich zum Gleichungseditor bietet.

Outlines

00:00

📅 Formatting Date Fields in IDEA

The speaker explains how to format a date field in IDEA that is not in the standard format. They mention two methods: using the equation editor and creating a custom function. The first method involves using a character to date function to reformat the date into a usable format. The speaker also discusses the drawbacks of this method, such as code reuse and readability. They then demonstrate how to create a new virtual date field and use the equation editor to apply the reformatting equation.

05:03

🔧 Custom Function for Date Formatting

The speaker introduces the concept of custom functions in IDEA, which allows for direct interaction with the database using iScript or Python. They guide through the process of creating a custom function named 'date change' to reformat a character date field. The speaker explains how to split the date string into year, month, and day components, handle different date formats, and then reassemble them into a standard format. They also mention the benefits of custom functions, such as readability and the ability to use loops and Python commands.

10:03

🔗 Applying Custom Functions and Equation Editor

The speaker concludes by demonstrating how to apply the custom function to the date field and use the character to date function within the equation editor to convert the formatted character field into a date field. They highlight the advantages of custom functions for complex equations and the additional functionality provided by Python custom functions, wrapping up the tutorial with a successful validation and application of the date formatting techniques.

Keywords

💡Date Field

In the context of the video, a 'Date Field' refers to a data field that contains date information. The speaker discusses how to format this field properly so it can be used effectively in a database or software like IDEA. The date field is expected to be in a standard format such as 'YYYY-MM-DD', but the speaker is dealing with a non-standard format that needs to be corrected.

💡Equation Editor

The 'Equation Editor' is a tool within the IDEA software that allows users to create or modify equations. It is used to transform the non-standard date field into a standard format. The speaker demonstrates how to use the Equation Editor to apply a formula that reformats the date field.

💡Custom Function

A 'Custom Function' in the video refers to a user-defined function created within the IDEA software using either IDEA Script or Python. This function can interact directly with the database and is used to perform complex operations on data, such as reformatting dates.

💡Character to Date

The 'Character to Date' function is used to convert a string of characters representing a date into a date object that the software can understand and work with. This is crucial in the process of reformatting the date field, as it allows the software to recognize and use the date information correctly.

💡IDEA Script

IDEA Script is a scripting language used within the IDEA software for automating tasks and performing complex data manipulations. It is one of the options for creating Custom Functions, allowing users to write scripts that can interact with the database.

💡Python Custom Function

A 'Python Custom Function' is a function created using Python, a widely-used programming language, within the IDEA software. This function can be used to perform more advanced operations and leverages the full power of Python, offering additional functionality beyond what IDEA Script can provide.

💡Reformatting

In the video, 'Reformatting' refers to the process of changing the structure or format of data, specifically dates, to make it compatible with the software's requirements. This is necessary when the date data is not in the expected format.

💡Virtual Date Field

A 'Virtual Date Field' is a field created within the software that does not physically exist in the database but is generated on-the-fly based on calculations or transformations of existing data. In this case, it is used to display the reformatted date in a usable format.

💡I-Split

The 'I-Split' function, similar to the 'AT SPLIT' function in the Equation Editor, is used in IDEA Script to split a string into parts based on a specified delimiter. It is used in the Custom Function to separate the year, month, and day components of the date.

💡I-String

The 'I-String' function in IDEA Script is used to manipulate strings, such as extracting a substring or finding the position of a character within a string. It is used in the Custom Function to locate and extract the date components.

Highlights

The speaker is discussing how to format a date field in an idea management system.

The date field needs to be in a standard format like years, months, and days.

The speaker will demonstrate two methods to format the date field: using the equation editor and a custom function.

The preferred method for complex scenarios is usually the custom function.

Creating a new virtual date field called 'idea date'.

Using the 'character to date' function to reformat the date.

The equation editor is used to develop a formula for date reformatting.

The custom function approach is introduced as an alternative to the equation editor.

Custom functions allow direct interaction with the database using iScript or Python.

The custom function 'date change' is created to reformat the date field.

Variables are created to hold the year, month, and day parts of the date.

The 'split' function is used within iScript to handle different date formats.

The speaker explains how to handle cases where the year is given in two or four digits.

The month and day parts of the date are reformatted to ensure they are two digits each.

The reformatted date parts are combined to create a standard date format.

The custom function is tested and validated within the idea management system.

The speaker compares the custom function method to the equation editor, highlighting the readability and functionality advantages.

The addition of Python custom functions expands the capabilities of the system.

Transcripts

foreign

[Music]

good day everyone in this second part

what I'm going to do is I'm going to

take a date field that isn't really in

an idea format

so as we can see here that the date

field generally it wants to be in years

months day or something similar to that

you know with two digits for the year

sorry four digits for the year two

digits for the month and two digits for

the day so we're missing the proper

formatting to actually be able to use

this properly in idea

so I'm going to show you two ways how we

can format this one with the equation

editor and the other one uh using a

custom function

my preference generally adds scenarios

like this is usually a custom function

and I'll sort of show you why and you

know as we go along and hopefully it'll

actually be obvious

so I'm just going to first create a new

field

I'm just going to call this idea date

and I'm going to make this a virtual

date field

then we'll bring up the equation editor

so I'm going to make this a bit larger

and I've already developed this equation

or actually

the person who sent me this had already

developed this so I'm just going to

reuse this

so you could sort of see how it works

and we'll go about explaining it I think

it'll probably be easier to explain than

every than anything else

uh so the first function here is

character to date so this is taking a

character

uh and changing it to a date so all this

part here is actually

taking

this date here and reformatting it so we

could use the character to date function

so what we're doing is we're returning

this function in here so that it has two

digits for the day with a dash two

digits for the M with a dash and four

digits for the year so the first part is

the day so we're using a lot if here and

what we're doing here is we're seeing

okay if the length of the first

characters are everything up to the

first hyphen hyphen here is only length

of one then we add on a zero to it if

not then we take everything up to the

one

so that is this part here it gives us

the date

sorry not the date that gives us the day

then we're including the hyphen

then the next part is we're doing the

same thing here we're using the at split

to figure out if the month

is actually one character and if it's

one character then we're adding on a

zero to it if not we're using it

so everything

from here to here is related to

reformatting the month into two digits

and finally here we are doing the same

thing for the

sorry for the year uh there's extra code

here where where somebody okay the date

could be two digits in which case we add

on the 20 if not we use the the whole

date

so what this will give us is something

that will be day day Dash mm Dash WHYY

which is this format here

and then using the character to date

we'll take this character this

reformatted character and turn it into a

date

now as you can see one of the problems

here is that we are

you know reusing the code over and over

again so it makes it kind of long and

remote and also the way it winds through

here it's kind of sometimes hard to read

but it works so we can try this out it's

a valid equation

and then I just click on OK and let's

see how this works

and here we've got the reformatted field

so now this is actually in a format of a

date field so now you can use any of the

usual IDF functions or analytics on this

date field

the second way I'm going to do this is

through a custom function

so again a custom function is through

the equation under so I'm going to add

on another date field

call this CF

again this will be another date field

but instead of creating this in the

equation editor I'm going to create it

as a custom function so a custom

function allows you to use idscrip or

python to create functions that will

interact directly with your database

through virtual fields

so I'm going to do a custom function so

I'm going to click click on new

so here I got the the uh I can use

either a custom custom function which

would be an idea script or the python

custom function the distance is I'm

going to use the idea script one

and this brings me up the editor I'm

just going to make this a bit larger so

first off I've got to give this a

function name

and I'm just going to call this date

change

then I gotta select the number of

parameters so we get our multiple

parameters here in this instance I only

want one parameter namely the that date

field but that date field is in a

character format so we're going to

select character

and I can add on some help tasks and

change the name if I want but I'm just

going to leave it for the defaults for

this demonstration

I click on OK

and now I start writing the

the item

so we also have a way to test the

equation so suppose I want to go in and

test the equation so suppose I want to

go in where we want to say

you know January 6

you know 2023.

so that's going to be our test

first thing I want to do to this is I

want to create variables to hold the

different parts of the date

so I'm going to

create a variable

to hold the year create a variable to

hold them a month and create a variable

to hold the date

so these are all strings because we want

the characters

because it's being so this is basically

a character field so I'm using character

variables to hold the different

information

now if I want to use the year

easy enough I can just use yes ear

equals 2. so on the S here here I don't

know whether it's going to be two

characters or four characters so what

I'm going to do is like sort of the same

thing as we did in uh the equation

editor is I'm going to use up the I

split the split function so you may not

be aware of this but the majority of the

at functions within the equation editor

are also available within idea script by

replacing the at symbol with the I

symbol so basically all functions that

were created

up to uh Version 9 of idea

are available

so the split function is one of them so

I can use that

so the Iceland is first off is I use the

the variables so the P1 so this is

holding the original date

then I am looking for the hyphen

and then basically the end so basically

putting two double quotes with nothing

in between means that it's either the

end or the beginning of the variable

depending on how you use it in this

instance because it's placed there it

means it's to the end and as there's two

hyphens there I'm saying I start from

the second one and going to the end

and we can see the track of that works

by basically doing big change so the

name of the function equals to the S

here

and then if I go click on test

it gives me 2023.

now the next thing I want to do is what

happens if it's only 23.

so in that instance then I just do this

year I'm sorry I'm going to do an if

if length s

the ears off the length is equal to

totally two characters

then it is equal to I want to add on

20

plus the ear

so if I try that

so it gives me 2023 which is the one I

want

so that's the first one now let's do the

the month

oops

and again here as I'm going to use the I

splits

for this time I'm going to use instead

of the empty quotes I'm going to use

hyphen hyphen and a one so what this

means is I'm splitting everything

between

the first hyphen and the second hyphen

so the one means it's the first one

and again here I'm going to just check

and see whoops

the length here

equals one

then we add on a zero

whoops

two of the months

and now if I try this let's see what we

get

zero and perfect and the final one is

the day

and today we can do this a bit

differently I'm going to do this a bit a

little bit differently so instead of

using because the days always the first

characters I could use a left

and I can say Okay I want anything left

starting from the first character and

then I'm going to locate the first time

that the hyphen shows up and how I do

that is a function called in string

and I'm going to say I want this as the

first character

and actually this is not a one I just

realized this is P1

so this is the variable that we'll be

looking at so here I'm going to be

looking at the starting from the first

character in the variable P1

with the hyphen

and I'm going to have to subtract one

reason being is that

here it's the second character but I

only want one so I have to subtract 1 in

that instance and then again I just do

the

the length again

equals one then as day equals

oops

and now we're going to try this out make

sure the day is working right

and so it is and then it's just a matter

of putting it together

so what we want is we want the year

plus the month

plus today

and we test that out so it's giving me

the proper format

so I'm still returning a character field

but now I can use the character to date

function within the equation editor and

also I'm going to add the category here

I'm going to add this as a date time

so here let me go I can go and validate

so there's no errors

I'll save it

I'll exit out of this here I'll exit out

of the custom functions so now if I go

into custom functions under date time we

can see we got the date change here

so I'll use that

and I'm using that on the date

and I'm going to wrap this around the or

actually use the

character

to date

and I'll just show the format is yyy

mmdd

to change it to a date field

and the validations is okay I click on

this and it process a dialog

and now you can see that the custom

function

gives you the same result

as the equation editor for me it's just

the custom function to when the

equations are a bit more complicated I

prefer custom functions because I find

they're a bit more readable plus also

for more advanced items you can also use

Loops in there and now with uh the

addition of being able to create python

custom functions you also have access to

all the uh you know python command so

that opens up a whole bunch of other

functionality that you can do within the

equation editor and custom functions

thanks