use calculated values with conditions on multiple colums

posted on April 15th, 2019

Closed

Eric Malalel asked on April 15, 2019

I have a report with colums for USER (FIRST_NAME, LAST_NAME, etc.) and two columns, COURSE_NAME and USER_STATUS.
COURSE_NAME may be NULL, which means this COURSE does not apply to this USER.
USER_STATUS may be NULL, which, if COURSE_NAME is not NULL, means user has not yet started the COURSE. Otherwise, USER_STATUS may be "Not validated" or "Validated".
Can I use FlexMonster calculated values, and how, to have an additionnal column "COMPUTED_STATUS" with value:

"Not applicable", if COURSE_NAME is NULL
"Not started", if COURSE_NAME is not NULL and USER_STATUS is NULL
"Not validated", if COURSE_NAME is not NULL and USER_STATUS is "Not validated"
"Validated", if COURSE_NAME is not NULL and USER_STATUS is "Validated"

If not possible with FlexMonster, I can probably handle it using a combination of CASE and IF in my SELECT statement.

1 answer

Public

Vera Didenko ⋅ Flexmonster ⋅ April 16, 2019

Hello, Eric,

Thank you for your question.

We would like to confirm that conditions can be specified on multiple columns, for example, COURSE_NAME is not NULL and USER_STATUS is NULL.

Please note that in Flexmonster it is not possible to compare values of type string in calculated fields.

1) One way to achieve the desired output is to use the customizeCell property together with the calculated field:

a) Define the calculated field:

"measures": [
{
"uniqueName": "COMPUTED_STATUS",
"formula": "if( count(\"COURSE_NAME\") == 0, 0, 
            if( isnan( sum(\"USER_STATUS\")), 1, 
            if(sum(\"USER_STATUS\") == 0, 2, 3)))",
"caption": "COMPUTED_STATUS"
}
]

b) Use the customizeCell property to make necessary changes in the column “COMPUTED_STATUS” to display the expected value:

customizeCell: function(cell, data) {
   if (data.measure != null && data.measure.uniqueName === "COMPUTED_STATUS") {
        if(cell.text == 0){
             cell.text = "Not applicable"
        }
        else if(cell.text == 1){
                  cell.text = "Not started"
        }
        else if(cell.text == 2){
                  cell.text = "Not Validated"
        }
        else if(cell.text == 3){
                  cell.text = "Validated"
        } 
   }
}

As a result, the cells of the “COMPUTED_STATUS” column will show text as desired.

Please note that numbers are used in calculated fields when comparing values.
Hence, we changed USER_STATUS data type from string to number the following way:

a) USER_STATUS = "Validated" we changed to USER_STATUS = 1
b) USER_STATUS = "Not Validated" we changed to USER_STATUS = 0

Here is a JSFiddle example for illustration.

2) Another way is to reformat the data on the server before passing the data to the client side as you mentioned earlier.

Please let us know if this works for you and if you have further questions.

Best Regards,
Vera

This question is now closed