Excel functions in Djeeni process step parameters

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.