|
Functions & External Lookup |
  
|
|
Functions & External Lookup |
|
In the relevant Tab you will find a feature to let you define if you want to perform an (or multiple) FUNCTION-OPERATION in a specified column
Functions can be "linked" together or not
The PARENT Functions display in RED and the CHILD ones in BLEU
The CONDITION ID the Function is linked to is displayed in the "L" column
LIST on all functions available :
Compact String :
This method removes all spaces and punctuation from a string. This can be very useful to create strings for sorting queues or tables where punctuation and spaces should not affect the sorting order. It is also very convenient when comparing strings where spaces and punctuation may differ, but the text is the same.
Depunctuate String :
This method can be used to remove all punctuation from a string. Retains figures.
Depunctuate + String :
This method can be used to remove all punctuation from a string. This includes all control characters with ASCII value less than 32.
Manual Formatting :
Create your own formatting value - ie @P ###-###-###P
You can also UPPERCASE or LOWERCASE a String or even CONCATENATE a STRING entered in the 'Value to Use' entry field BEFORE or AFTER the value of the selected Source Column value
If you want a space concat separator then type in 'space' (this is the only time when it is needed to 'write' space)
You can also use only the X LEFT or RIGHT characters from the source column's value
You can also extract a SUBSTRING starting at X position and of Y length of the source column's value
You can also add delete multiply or divide by a PERCENTAGE the value of a column.
You can use a FORMAT or a DEFORMAT function on your data and see onscreen the format selected
You can also use a DEFAULT value to be used (like to append a '.' at the end of a column value).
You can also CAPITALIZE a column to clean it out of all uppercased entries by users
Here are the symbols used to 'separate' words and capitalize the 1st of each separated group
' : quote
. : period
- : hyphen
_ : underscore
/ : division bar
STORE FUNCTION RESULT
If you are applying several functions to obtain an "end" result then you need to STORE this "end" result by checking this option box
example : DEFORMAT a value then SUBSTRING to extract a portion - this "end" result will be STORED (and used if checked) on the NEXT function
CONCAT ALL STORED RESULTS
If you are applying several functions (with or without STORING them) you can define to CONCAT all STORED previous results by checking this option box.
example : on ColumnA DEFORMAT then SUBSTRING -> STORE | on ColumnB DEFORMAT then SUBSTRING -> STORE - here you can CONCAT the ColumnA "end" result with this ColumnB "end" result by checking this option box so as to do a last External Table LOOKUP and work on that CONCATENATED "total end" result as a value to use in the LOOKUP.
ADVANCED CAPITALIZING :
Define the 3 Tables (under Navigation Panel Setup child entries) containing "lower" "upper" and "pairs"
"lower" : contains WORDS to be ALWAYS lowercased such as : and if in on else .....
"upper" : contains WORDS to be ALWAYS uppercased such as : NE NW SE SW .....
"pairs" : contains WORDS to be ALWAYS matched and capitalized as per YOUR values : ITUNES -> iTunes | D' -> d' | MACD -> MacD | ST -> St ....
During a data transfer task DMC will read these tables and search any corresponding "match" to apply the relevant rule depending on which table contains the "match"
Parse (n) : will extract from a STRING the N th.WORD
If the value of (n) entered is bigger than the number of words in the column at runtime, then the LAST word will be extracted.
Example :
If you have a column in destination for Price with VAT and in the source Table only a column with Price Vat less - in this case you check the OPERATION box and define to perform a MULTIPLICATION on the source column which you chose in the listbox and o use an operand of say 0.83612 to apply on that columns value at transfer time
DMC will take the source column value and multiply it by the operand and fill the destination selected column with the result
IF you define several Functions on the SAME source column (non linked ones) :
DMC will consider them in SEQUENCE and depending on the ORDER they are defined will apply the 1st then take the result and apply the 2nd on this result etc ....
Example : You can start by using a SUBSTRING to extract a portion of the source column
Then use a LEFT or RIGHT to again use only a portion of the portion extracted earlier
Then use a DEFORMAT to convert from a STRING to a LONG a DATE stored in a String source
And finally apply a FORMAT to define the extracted LONG (date) into a display readable format
EXTERNAL TABLE LOOKUP (during a Data Transfer to an SQL table ONLY)
You can perform (in each or any columns of the destination Table) ONE external Table lookup to retrieve a value contained in ANOTHER SQL table from the SAME DB
1. Select External Lookup
2. From the tables drop down select the Table to query
3. From the Columns drop down select the one to retrieve the value to use in the current destination Column
4. You now have to "link" TWO columns from the current table and the External Table to act as IDENTIFIERS (to build a proper WHERE clause in sql language - DMC will do it for you)
(ie Destination table contains a Columns called ClientName and the External Table has the SAME Column (or a different Name) but ALSO containing the SAME VALUE so you need to "link" them together
5. Your query can be built like this on up to 4 different columns to create uniqueness.
select SearchColumn from ExternalTable where LookupColumn = 'LookupValue' [ and where LookupColumn2 = 'LookupValue2' ] etc ...
If your lookup can return with an empty value of the selected WHERE clause, you can add a second lookup using a different WHERE clause
(ie : you are not sure all records contains a ZIPCODE value (as in this example) but then some others can contain a STATECODE one)
The you define several External Lookups on the same column (lookup columnA where ZIPCODE = ZIPCODE and a second one where STATECODE = STATECODE) etc :
You can use the Stop processing at First return option so as to avoid processing the other lookups
Filter on Error : Define this function to act as a FILTER (no return from lookup) and NOT transfer the RECORD