Page Comparison

This sample file contains items with values for quarters and the two half-years.

As a result, the sum of the half-year values per item is to be imported.

The desired fields can be selected via the button "Create new entry".

In this example, a new entry was created for the field "Column 7" and the field "Column 8". The result is transferred to the column "Column (9)".

The entry for the parameters is now "7;8". With a SmartNotes version < 2.9, these values must be entered manually.

In the position preview, the sum is now displayed as a value.

In this example, we want to determine and transfer the sum of the quarters.

So the fields Q1 to Q4.

By clicking on the "Parameter" field, the "Range Selection" dialogue appears. In this we select the "From column" and the "To column".

The entry for the parameters is now "column3,column6". With a SmartNotes version < 2.9, the expression "3,6" must be entered manually.

In the position preview, the sum is now displayed as a value.

In this example, we want to determine and transfer the sum of the quarters.

So the fields Q1 to Q4.

To realise this, we need the document variables Q1 (with value column3) and Q4 (with value column6). Please pay attention to upper and lower case.

The entry for the parameters is "%Q1%,%Q4%".

In the position preview, the sum is now displayed as a value.

In this example, we want to determine and transfer the sum of the quarters.

So the fields Q1 to Q4.

To realise this, we now need the document variables Q1 (with value column3), Q2 (with value column4), Q3 (with value column5) and Q4 (with value column6). Please pay attention to upper and lower case.

The entry for the parameters is now "%Q1%;%Q2%;%Q3%;%Q4%".

In the position preview, the sum is now displayed as a value.

The column "column3" contains contents of different lengths. Since we need this column for a later "Join", we bring the fields to a uniform length.

The length of the field should be 8 characters, we want to fill the empty spaces with an underscore. The entry for the parameter is "8,_" and must be made without inverted commas.

The change is displayed in the "Processed data preview" overview.

For our RFBILA to be imported, we have already applied the function "FilterNumeric". As a result, we already get only the lines marked in green from the extract shown.

In this import we want to start a mapping for the positions without account number. For these, the empty name field bothers us.

So we use the filter "Filter" and display all lines where the field "Name" is empty.

As a result, we now get all items in the preview that have a name instead of the account number.

In the next step, we would then transfer the content from "Account" into the empty "Name" fields.

This can easily be done with the function "Join" with the parameter "Account" for the field "Name".

The excerpt of an RFBILA shows that sometimes you even need several mappings for one import file to import all the data (in this case there will be two).

In this example we see that some items have values but no account number.

But we also see rows where there is nothing in the Value column. So there is no zero value either.

Since we do not need these rows and want to get a better overview for further mapping, we first insert the function "FilterNotEmpty".

In our example we apply the function "FilterNotEmpty" to the column "Value (11)". This contains our values and of course must not be empty.

In the preview and the later import, the lines are no longer present.

We had already adapted the RFBILA shown in the example "FilterNotEmpty" for the import.

This example should make the difference between "FilterNotEmpty" and "FilterNumeric" clear.

In the present RFBILA, there are rows where the field "Value" is empty. However, there are also a few cells that contain text there.

With the function "FilterNotEmpty", the rows with text would also be taken over. The function "FilterNumeric" hides this cell.

In our example, we apply the function "FilterNumeric" to the column "Value (11)". This contains our programming (accounts) and in our case may only contain numbers.

In the preview and the later import, there are neither empty cells nor cells with text.

The import file was extended by one column because it is to be filtered according to an accounting area.

For filtering, we have created a document variable BK and given it the value 200.

Filtering by variables is an ideal option if you work with the Factory module, for example, and want to store different values for the sub-documents.

The value for the company code is contained in the column "column2".

We therefore need the column "column2", select the function "FilterVar" and enter the expression %AE% as a parameter.

Die Datenvorschau zeigt als Ergebnis nun alle gefilterten Datensätze an.

In the example, the company code (column2) is to be appended to the accounting area (column1). We use the character "#" for optical separation. We have already prepared this in the "Postfix" example.

The "Join" function is created for the column "column1" and the column "column2" is selected as a parameter via the selection menu.
SmartNotes versions < 2.9 do not yet have the selection menu. The parameter "column2" is to be entered there directly.

The column "column1" has now been extended by the character "#" and the value from column "column2".

For further processing, the contents of column "column4" are to be appended to those of column "column2".

In order to be able to react flexibly to changes, we define the document variable "AE_Extension".
When using the Factory module, this method is particularly helpful if different fields have to be addressed in the individual Sud documents.

The function "JoinVar" is created for the column "column2" and the variable "%AE_Extension%" is entered as a parameter.

The column "column2" has now been extended by the values from column "column4".
In doing so, "column4" was filled with "0" to 8 digits via the "Fill" function (Processing 1).

In this case, in addition to the account number (column1) and the name (column3), we need the column "column11" as a value.

However, the values of column11 are in TSD€ and we need them in unit 1.

In this case, the input is column "column11" with function "Multiply" and parameter "1000".

The values are now correctly displayed in the preview and taken over accordingly during the later import.

In the present import file, column 1 (column1) shows the account numbers. In a later step we want to link these with the accounting area (column2).

In the first step, we need an optical separator after the account number for the later link. We want to use the "#" (hash) as a character.

The following entries are necessary for this. The column is "column1", the function is "Postfix" and the parameter is "#".

The column "column1" is now extended by the entry "#".

In the present import file, the account numbers are shown in column 1 (column1), the company code in column 2. Both values are to be linked and receive the character "#" as a visual separator.

For the column "column1", the function "PostfixJoin" is created with the parameter "#,2".

In the data preview, the column "column1" is now linked to the column "Column2", visually separated by a "#".

In this example, the column "column2" contains the company code that is to be appended to the column "column1" later on.

Before that, the abbreviation "AE" is to be placed in front of the company code.

For this purpose, the function "Prefix" with the parameter "AE" is created for the column "column2".

The column "column2" now contains the expression "AE" and the value of the accointing area.

In our import file, a few account designations "column3" contain the expressions "BK100", "BK200", "BK300". We remove these expressions with the help of the function "Remove". 

For this purpose, the function "Remove" with the parameter "BK100" is created for the column "column3".

Since we are dealing with three different expressions, we need another function for the parameters "BK200" and "BK300".

The expressions "BK100", "BK200" and "BK300" are now no longer displayed in the result.

In the import file, the name (column2) contains control characters in the form of "\\".

To remove these, the function "RemoveUnsupportedCharacters" is used for the columns "column2", without Parameter.

The control characters are no longer included in the preview and the import.

After a change in the previous system, the import file now contains the expression "Score" instead of "Result" for some names. This should not be imported into the report. The term should be replaced.

Für die betroffene Namensspalte „column3" wird die Funktion „Replace" mit dem Parameter „Scores,Results" anlegt.

In the preview and the later import, the affected lines now contain the expression "Results".

In the import file, an item does not have the name "column2". Based on the account number, it can be determined that it must be the name "Personnel Expenses".

For the name column "column2", the function "ReplaceEmptyWith" is created with the parameter "Personnel Expenses".

In the preview and the later import, the affected row now contains the expression "Personnel Expenses".

Since an update in the previous system, the delivered import file is sorted by description (column3).

For the processing of the further mapping we would like the sorting by accounting area (column2).

For this purpose, an entry for account number (column2) with the function "Sort" and the parameter "asc" is created as an entry in the data processing.

We can now carry out further processing of the mapping with the sorted column (column1).

In the import file, the name (column3) contains the entry "- BKnnn" for some items. 
The entry is to be removed, especially since the company code is specified in the column "column2".

We want to separate the name from the separator "-". The separation creates two expressions, of which we need the first expression.

The input for the column "column1" is therefore function "Split" with the parameter "-,1".

The preview now shows the names without the second part "- BKnn".

In the present import file, the name columns (column2) start with the expressions "DACH" or "EMEA". We do not want to import these.

Since the expressions have the same length at the beginning, we can take the name column from the sixth character. Since the names are not very long, we select the 40 characters after that.

The input for the column "column2" is therefore: Function "Substring" with the parameter "6,40". 

The number of characters to be taken over can be chosen longer than actual characters are present. It is therefore not necessary to determine the exact length.

In the preview and the later import, the unwanted expressions are now no longer present.

In the import file, the values per sub-item "Position" are delivered for the items in "column1".

The result should be the sum of all values per main position.

To get the desired result, two functions must be inserted:

The function "TakeOver" takes over the values of the previous row if there is no value. This is to be done for the column "Position". A parameter is not necessary. The function must be in the first position (Verarb. Index = 0).

The following function "Sum" then adds up the values of all items with the same name in the column "Position". In the example, this would be the sum of all records for "10H2", "50000000", etc.

In the preview, each group element is now displayed with the sum (EUR) of all sub-items.

Without the "TakeOver" function, a total sum would be formed for the empty columns (Programming).

In the present import file, the details for account (Position) and designation (Description) are not included in each row.

Since we also need the individual sub-items, the empty columns are to be filled with the previous values.

In this case, we need the function "TakeOver" for the columns Account (column1) and Designation (column2), each without parameters.

In the preview, the transferred values are now displayed and later also imported.