Unknown syntax error in calculated field

Question & Answer
,
1

Hallo!

I am trying to create a calculated field with syntax below but I get an error “The arguments to function CASE must be of the same type”

ifelse(
  (
    locate('Deice', concat(${Incident}, '')) > 0 AND {Deice} = 'Performed'
  ) OR (
    locate('SAFA Ramp Check', concat(${Incident}, '')) > 0 AND {SAFA Ramp Check} = 'True'
  ) OR (
    locate('FMS Update', concat(${Incident}, '')) > 0 AND {FMS Update} = 'True'
  ) OR (
    locate('Occurrence', concat(${Incident}, '')) > 0 AND {Occurrence} = 'True'
  ),
  'Show',  
  'Hide'   
)

I don’t really understand what fails here. The parameter ${Incident} takes multiple string values.
Can someone help me troubleshoot?

image
image845×278 32.1 KB

2

Hi @Fotis_flex
what do you want to achieve with the concat(${Incident}, '')?
As you are not working with the postion of the locate i would try in('Deice', ${inc}) AND {Deice} = 'Performed'
BR

3

Hi @ErikG

Indeed, I applied your suggestion and worked. But now i have another problem. I have a dataset with 5 fields, Field1-5. Field1 is intiger, Field2 takes values “Performed”/“Not Performed” and the rest take “True”/“False”.

I want to create a table which lists Field1 values and a control on it. The control should be a drop-down list that the user can select either Field2 , 3, 4, 5, or all of them. Based on this selection, the table must be filtered with condition below:
If user selects Field2 then Field2 must be of value “Performed” and table should only include these records
For Field3,4,5 the value must be equal to “True” and table must be filtered accordingly.

Finally, if user selects all of them, apply both above conditions.

Currently, I have created a parameter ${Incident} giving it multiselect values to be:Field2, Field3, Field4, Field5. Then, I add it as a control for the user to select whichever desire. Then I create calc field with the formula you suggested and filter table setting this field to be “Show”
This fails though because in the control I get what you see in pic. What am I doing wrong? How can I reach my goal?
Many many thanks for your time!

image
image578×256 19.8 KB

4

Hi @Fotis_flex
any chance you can create a sample in Arena and share it with us?
BR

5

Hi @ErikG

I am not sure I understand what you mean by “create a sample in Arena”? What is Arena?!

6

Go to Console and create a sample. Will be better to follow.

grafik
grafik332×168 10.4 KB

7

Hi @ErikG Thanks for clarifying that! I replicated the analysis in Arena. Here is it’s definition:

"Definition": {
    "DataSetIdentifierDeclarations": [
        {
            "Identifier": "dataset",
            "DataSetArn": "arn:aws:quicksight:us-east-1:1234XXXXXXXX:dataset/DSET-CU-7258-dataset"
        }
    ],
    "Sheets": [
        {
            "SheetId": "7aa1abb8-4166-45a0-bc78-f9ee9b3551ce",
            "Name": "Sheet 1",
            "ParameterControls": [
                {
                    "Dropdown": {
                        "ParameterControlId": "b58d3051-00f8-42d3-af7b-30b2875d882a",
                        "Title": "Incidents",
                        "SourceParameterName": "Incidents",
                        "DisplayOptions": {
                            "SelectAllOptions": {
                                "Visibility": "VISIBLE"
                            },
                            "TitleOptions": {
                                "Visibility": "VISIBLE",
                                "FontConfiguration": {
                                    "FontSize": {
                                        "Relative": "MEDIUM"
                                    }
                                }
                            }
                        },
                        "Type": "MULTI_SELECT",
                        "SelectableValues": {
                            "Values": [
                                "deice",
                                "fmsupdate",
                                "occurence",
                                "safarampcheck"
                            ]
                        }
                    }
                }
            ],
            "Visuals": [
                {
                    "TableVisual": {
                        "VisualId": "641f7790-c337-417b-a1ab-fe6cd40f14b1",
                        "Title": {
                            "Visibility": "VISIBLE"
                        },
                        "Subtitle": {
                            "Visibility": "VISIBLE"
                        },
                        "ChartConfiguration": {
                            "FieldWells": {
                                "TableAggregatedFieldWells": {
                                    "GroupBy": [
                                        {
                                            "NumericalDimensionField": {
                                                "FieldId": "s3LogicalTableMap.flightid.0.1741002225437",
                                                "Column": {
                                                    "DataSetIdentifier": "dataset",
                                                    "ColumnName": "flightid"
                                                }
                                            }
                                        },
                                        {
                                            "CategoricalDimensionField": {
                                                "FieldId": "s3LogicalTableMap.deice.1.1741002234171",
                                                "Column": {
                                                    "DataSetIdentifier": "dataset",
                                                    "ColumnName": "deice"
                                                }
                                            }
                                        },
                                        {
                                            "CategoricalDimensionField": {
                                                "FieldId": "s3LogicalTableMap.safarampcheck.3.1741002246107",
                                                "Column": {
                                                    "DataSetIdentifier": "dataset",
                                                    "ColumnName": "safarampcheck"
                                                }
                                            }
                                        },
                                        {
                                            "CategoricalDimensionField": {
                                                "FieldId": "s3LogicalTableMap.fmsupdate.2.1741002241891",
                                                "Column": {
                                                    "DataSetIdentifier": "dataset",
                                                    "ColumnName": "fmsupdate"
                                                }
                                            }
                                        },
                                        {
                                            "CategoricalDimensionField": {
                                                "FieldId": "s3LogicalTableMap.occurence.4.1741002250707",
                                                "Column": {
                                                    "DataSetIdentifier": "dataset",
                                                    "ColumnName": "occurence"
                                                }
                                            }
                                        }
                                    ],
                                    "Values": []
                                }
                            },
                            "SortConfiguration": {}
                        },
                        "Actions": []
                    }
                }
            ],
            "Layouts": [
                {
                    "Configuration": {
                        "GridLayout": {
                            "Elements": [
                                {
                                    "ElementId": "641f7790-c337-417b-a1ab-fe6cd40f14b1",
                                    "ElementType": "VISUAL",
                                    "ColumnIndex": 0,
                                    "ColumnSpan": 31,
                                    "RowIndex": 0,
                                    "RowSpan": 18
                                }
                            ]
                        }
                    }
                }
            ],
            "SheetControlLayouts": [
                {
                    "Configuration": {
                        "GridLayout": {
                            "Elements": [
                                {
                                    "ElementId": "b58d3051-00f8-42d3-af7b-30b2875d882a",
                                    "ElementType": "PARAMETER_CONTROL",
                                    "ColumnSpan": 2,
                                    "RowSpan": 1
                                }
                            ]
                        }
                    }
                }
            ],
            "ContentType": "INTERACTIVE"
        }
    ],
    "CalculatedFields": [
        {
            "DataSetIdentifier": "dataset",
            "Name": "__IncidentCondition__",
            "Expression": "ifelse(\n  (\n    in('deice', ${Incidents}) AND deice = 'Performed'\n  ) OR (\n    in('safarampcheck', ${Incidents}) AND safarampcheck = 'True'\n  ) OR (\n    in('fmsupdate', ${Incidents}) AND fmsupdate = 'True'\n  ) OR (\n    in('occurence', ${Incidents}) AND occurence = 'True'\n  ),\n  'Show', \n  'Hide'   \n)"
        }
    ],
    "ParameterDeclarations": [
        {
            "StringParameterDeclaration": {
                "ParameterValueType": "MULTI_VALUED",
                "Name": "Incidents",
                "DefaultValues": {
                    "StaticValues": [
                        "deice",
                        "fmsupdate",
                        "occurence",
                        "safarampcheck"
                    ]
                },
                "ValueWhenUnset": {
                    "ValueWhenUnsetOption": "RECOMMENDED_VALUE"
                }
            }
        }
    ],
    "FilterGroups": [
        {
            "FilterGroupId": "b5b6bad0-3ca6-4a99-9be8-5ba175d78676",
            "Filters": [
                {
                    "CategoryFilter": {
                        "FilterId": "95e202fe-3937-45fe-895b-ce6447f5afd3",
                        "Column": {
                            "DataSetIdentifier": "dataset",
                            "ColumnName": "__IncidentCondition__"
                        },
                        "Configuration": {
                            "FilterListConfiguration": {
                                "MatchOperator": "CONTAINS",
                                "CategoryValues": [
                                    "Show"
                                ],
                                "NullOption": "NON_NULLS_ONLY"
                            }
                        }
                    }
                }
            ],
            "ScopeConfiguration": {
                "SelectedSheets": {
                    "SheetVisualScopingConfigurations": [
                        {
                            "SheetId": "7aa1abb8-4166-45a0-bc78-f9ee9b3551ce",
                            "Scope": "SELECTED_VISUALS",
                            "VisualIds": [
                                "641f7790-c337-417b-a1ab-fe6cd40f14b1"
                            ]
                        }
                    ]
                }
            },
            "Status": "ENABLED",
            "CrossDataset": "SINGLE_DATASET"
        }
    ],
    "AnalysisDefaults": {
        "DefaultNewSheetConfiguration": {
            "InteractiveLayoutConfiguration": {
                "Grid": {
                    "CanvasSizeOptions": {
                        "ScreenCanvasSizeOptions": {
                            "ResizeOption": "FIXED",
                            "OptimizedViewPortWidth": "1600px"
                        }
                    }
                }
            },
            "SheetContentType": "INTERACTIVE"
        }
    },
    "Options": {
        "WeekStart": "SUNDAY"
    }
}

}

8

I am not sure how to provide you with access to dataset. I only see this but do not know how to share it with you?

image
image1084×344 16.5 KB

9

@ErikG Hallo! Is there any update on this?

10

Hi,
you can get the link via
grafik
BR