Djeeni is created for people who have built up experience and proficiency in MS Excel by using it regularly for different data processing tasks. We do want you to be able to keep and utilize your proficiency and therefore built Djeeni to seamlessly work together with MS Excel and its powerful built-in functions. In the sections below we explain through practical examples how and for what purpose you can embed Excel functions at different Djeeni process steps.
Most of the processes are not linear: they can contain repeated steps (see lists) or conditional/optional steps that must be performed only if certain conditions are met. Djeeni provides the If process step to describe the conditions.
One of the most common conditions is to check if a cell is empty. The best practice is to use the ISBLANK() Excel function as it works all possible cell type:
If Djeeni name: ifEmpty Condition: ISBLANK([$wsWorksheet!B3]) 'check if cell Worksheet!B3 is empty
In MS Excel, you can set a dynamic value to a cell using a formula. Such a formula performs some calculations using a) values of one or more other cells; and b) built-in or user-defined functions. The formula is attached fixed to the cell.
In Djeeni you can keep every knowledge and experience that you have with formulas and Excel functions. Example: Let's suppose that we need to set the value of cell B4 on the worksheet wsMonthEnd by summarizing some values in the range G2:G20 on the worksheet wsERPData. In Djeeni you use the well-known SUM function with the same range parameter than in Excel within a [+….] Djeeni formula:
Cell Set Cell: wsMonthEnd!B4 'NB: Cells are always referred with their worksheet Value: [+SUM([:wsERPData!G2:G20])] 'A range is specified in [:....]
But Djeeni can do more. You can dynamically define also the cell using a Djeeni formula. Example: the sum that we gather must be set to B4 if it is less than 10000; otherwise it should go to C4:
Cell Set Cell: [+IF(SUM([:wsERPData!G2:G20])<10000,[$wsMonthEnd!B4],[$wsMonthEnd!C4])] 'Cell address is Value: [+SUM([:wsERPData!G2:G20])] 'referred by [$.....]
This Djeeni feature is very handy when used within a workbook, row, or column list. Example: an order worksheet (wsExport) is exported from an IT system and it contains thousands of rows with different dates (column B) and product codes (column K) in a month. Based on the date and the product code, each order must be listed in three pre-defined columns per week (15 target columns altogether for the whole month).
The solution on paper:
Go through the rows of wsExport Set the order value after the last row in the target column where the target column is calculated by: - the number of weeks from the month start using the WEEKNUM function; plus - the column within a week according to the product category using the IFS function
The solution in Djeeni (first temporary storing the target column calculation to cell wsTarget!ZZ1):
Row List Start Row: 2 End Row: #RowEnd Cell Set Cell: wsTarget!ZZ1 'Temporary cell to store the target column calculation result Value: [+ WEEKNUM([$wsExport!B#],2)–WEEKNUM(DATE(YEAR([$wsExport!B#]),MONTH([$wsExport!B#]),1),2)+1 + IFS([=wsExport!K#]<5000,0,[=wsExport!K#]<20000,1,[=wsExport!K#]>19999,2) ] Cell Set Cell: wsTarget![=wsTarget!ZZ1][#RowEnd|[=wsTarget!ZZ1]] Value: wsExport!C# Row List Next
If you are familiar with Pivot tables in MS Excel then this feature of Djeeni is an extension of the Pivot for more complex cases.
Working with dates is one of the most difficult tasks in MS Excel on three levels:
MS Excel puts a lot of undocumented effort to find and silently convert different values to dates. It makes a lot of errors: cannot find valid dates (because of the regional settings); recognizes dates where there are none; or simply misinterprets dates (e.g. makes 5th March from 3rd May) causing troubles.
Based on these issues we suggest that you use dates in Djeeni processes following these guidelines:
There is a hidden concept in MS Excel about what it means to reference a cell. When a cell (e.g. B3) is referenced in a formula or inside a function, MS Excel reads the value of the cell and uses the value further. Reading the value means also to decide if it is a number or a text or a date value (the type of the value). Certain function parameters can be only text or number or date values and MS Excel gives an error if the given value does not have the proper type. Therefore it is important to understand how Djeeni formulas treat cells and cell values when they are combined with Excel functions.
In Djeeni, cell references are always in the form wsDjeeniName!ColumnRow that can be resolved for MS Excel in two forms:
On the other hand, MS Excel expects “” around text values in functions. Example: If the value of the above A2 cell must be compared to 'Toy Boat' one would write in Djeeni: IF([=wsExport!A2] = “Toy Boat”,1,2); but this results in error. After processing the Djeeni formula MS Excel would see: IF(Toy Ship = “Toy Boat”,1,2).
There are two solutions:
Check always which solution fits in the formula.