Forum Discussion

enmiwong's avatar
enmiwong
Cloud Apps
08-23-2024

Applied JAQL formula on top of another JAQL formula result

Hi,

I have applied a JAQL formula to get new values (the values * 10), after that, I want to get the MAX from the result of the first query. how to achieve that?

this is the result get from JAQL :

 [{"data": 4.3,"text": "4.3"}, {"data": 1,"text": "1"}, {"data": 43,"text": "43"}]

the third column is get by applied this JAQL formula: 

{
"jaql": {
"context": {
"[sepal_length]": {"dim": "[irisdataset6.sepal_length]"}
},
"formula": "10 * MAX([sepal_length]) + 0",
"title": "transform_x"
}
}

now I want to get the max from the third column, how to achieve that?

Angular
Developers
How-To
Typescript

5 Replies

  • Helena_qbeeq's avatar
    Helena_qbeeq
    Cloud Apps
    08-26-2024

    Hi enmiwong ,

    If I understand your question correctly, in the third column you would like to get the maximum value previously obtained on the rows.

    Have you tried using multi-pass aggregation?

    In your example, it would look like this:

    MAX(DIMENSION_IN_FIRST_COLUMN, (10 * MAX([sepal_length]) + 0))

    I am attaching an example based on the eCommerce sample data.

    Please let me know if this works for you.

     

    Always here to help,
    Helena from QBeeQ
    [email protected]
    QBeeQ - Gold Implementation and Development Partner

     

     

  • DRay's avatar
    DRay
    Admin
    08-27-2024

    Hello enmiwong,

    I’m following up to see if the solution offered by Helena_qbeeq worked for you.

    If so, please click the 'Accept as Solution' button on the appropriate post, that way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.

  • enmiwong's avatar
    enmiwong
    Cloud Apps
    08-28-2024

    Hi, what is the DIMENSION_IN_FIRST_COLUMN ? is that a context key for the formula?

    one more question, how to do filter based on the transformed values?

      • enmiwong's avatar
        enmiwong
        Cloud Apps
        08-31-2024

         

         

        {
  "headers": [
    "histogram-default___x--sepal_length",
    "histogram-default___y--sepal_length",
    "transform_x"
  ],
  "metadata": [
    {
      "jaql": {
        "dim": "[irisdataset6.sepal_length]",
        "title": "histogram-default___x--sepal_length"
      }
    },
    {
      "jaql": {
        "agg": "countduplicates",
        "dim": "[irisdataset6.sepal_length]",
        "title": "histogram-default___y--sepal_length"
      }
    },
    {
      "jaql": {
        "context": {
          "[sepal_length]": {
            "dim": "[irisdataset6.sepal_length]"
          },
          "[sepal_width]": {
            "dim": "[irisdataset6.sepal_width]"
          }
        },
        "formula": "(1* MAX([sepal_length])' )+ (-1* MAX([sepal_width])) + 0",
        "title": "transform_x"
      }
    }
  ],
  "datasource": {
    "revisionId": "TBD",
    "fullname": "LocalHost/irisdataset6_PathWave_fa19bddef76d43688f7ae03eb5ef194f"
  },
  "processingInfo": {
    "cacheType": "result",
    "cacheTime": "2024-08-31T04:37:34.891Z"
  },
  "translationInfo": {
    "translationServiceProvider": "NewTranslationService",
    "isTranslationFallback": false,
    "isQueryFallback": false,
    "translationDuration": 0.008,
    "sqlFromCache": false
  },
  "values": [
    [
      {
        "data": 4.3,
        "text": "4.3"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 4.4,
        "text": "4.4"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 1.2000000000000002,
        "text": "1.2"
      }
    ],
    [
      {
        "data": 4.5,
        "text": "4.5"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 2.2,
        "text": "2.2"
      }
    ],
    [
      {
        "data": 4.6,
        "text": "4.6"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 0.9999999999999996,
        "text": "1"
      }
    ],
    [
      {
        "data": 4.7,
        "text": "4.7"
      },
      {
        "data": 2,
        "text": "2"
      },
      {
        "data": 1.5,
        "text": "1.5"
      }
    ],
    [
      {
        "data": 4.8,
        "text": "4.8"
      },
      {
        "data": 5,
        "text": "5"
      },
      {
        "data": 1.4,
        "text": "1.4"
      }
    ],
    [
      {
        "data": 4.9,
        "text": "4.9"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 1.3000000000000003,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5,
        "text": "5"
      },
      {
        "data": 10,
        "text": "10"
      },
      {
        "data": 1.4,
        "text": "1.4"
      }
    ],
    [
      {
        "data": 5.1,
        "text": "5.1"
      },
      {
        "data": 9,
        "text": "9"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5.2,
        "text": "5.2"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 1.1000000000000005,
        "text": "1.1"
      }
    ],
    [
      {
        "data": 5.3,
        "text": "5.3"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 1.5999999999999996,
        "text": "1.6"
      }
    ],
    [
      {
        "data": 5.4,
        "text": "5.4"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 1.5000000000000004,
        "text": "1.5"
      }
    ],
    [
      {
        "data": 5.5,
        "text": "5.5"
      },
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5.6,
        "text": "5.6"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 2.5999999999999996,
        "text": "2.6"
      }
    ],
    [
      {
        "data": 5.7,
        "text": "5.7"
      },
      {
        "data": 8,
        "text": "8"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5.8,
        "text": "5.8"
      },
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 1.7999999999999998,
        "text": "1.8"
      }
    ],
    [
      {
        "data": 5.9,
        "text": "5.9"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 2.7,
        "text": "2.7"
      }
    ],
    [
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 2.6,
        "text": "2.6"
      }
    ],
    [
      {
        "data": 6.1,
        "text": "6.1"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 3.0999999999999996,
        "text": "3.1"
      }
    ],
    [
      {
        "data": 6.2,
        "text": "6.2"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 2.8000000000000003,
        "text": "2.8"
      }
    ],
    [
      {
        "data": 6.3,
        "text": "6.3"
      },
      {
        "data": 9,
        "text": "9"
      },
      {
        "data": 2.9,
        "text": "2.9"
      }
    ],
    [
      {
        "data": 6.4,
        "text": "6.4"
      },
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 3.2,
        "text": "3.2"
      }
    ],
    [
      {
        "data": 6.5,
        "text": "6.5"
      },
      {
        "data": 5,
        "text": "5"
      },
      {
        "data": 3.3,
        "text": "3.3"
      }
    ],
    [
      {
        "data": 6.6,
        "text": "6.6"
      },
      {
        "data": 2,
        "text": "2"
      },
      {
        "data": 3.5999999999999996,
        "text": "3.6"
      }
    ],
    [
      {
        "data": 6.7,
        "text": "6.7"
      },
      {
        "data": 8,
        "text": "8"
      },
      {
        "data": 3.4000000000000004,
        "text": "3.4"
      }
    ],
    [
      {
        "data": 6.8,
        "text": "6.8"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 3.5999999999999996,
        "text": "3.6"
      }
    ],
    [
      {
        "data": 6.9,
        "text": "6.9"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 3.7,
        "text": "3.7"
      }
    ],
    [
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 3.8,
        "text": "3.8"
      }
    ],
    [
      {
        "data": 7.1,
        "text": "7.1"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.1,
        "text": "4.1"
      }
    ],
    [
      {
        "data": 7.2,
        "text": "7.2"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 3.6,
        "text": "3.6"
      }
    ],
    [
      {
        "data": 7.3,
        "text": "7.3"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.4,
        "text": "4.4"
      }
    ],
    [
      {
        "data": 7.4,
        "text": "7.4"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.6000000000000005,
        "text": "4.6"
      }
    ],
    [
      {
        "data": 7.6,
        "text": "7.6"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.6,
        "text": "4.6"
      }
    ],
    [
      {
        "data": 7.7,
        "text": "7.7"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 3.9000000000000004,
        "text": "3.9"
      }
    ],
    [
      {
        "data": 7.9,
        "text": "7.9"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.1000000000000005,
        "text": "4.1"
      }
    ]
  ]
}

        The json body attached is the response of JAQL query. the third column is retrieved by apply the formula: "(1* MAX([sepal_length])' )+ (-1* MAX([sepal_width])) + 0".

        now I want to do some operation to the third column, filter, sort in ascending order.... not sure how to do that