Applied JAQL formula on top of another JAQL formula result
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-23-2024 02:18 AM
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?
Labels:
- Labels:
-
Angular
-
Developers
-
How-To
-
Typescript
5 REPLIES 5
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-26-2024 05:21 AM
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
helena.ferreira@qbeeq.io
QBeeQ - Gold Implementation and Development Partner
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-27-2024 12:35 PM
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.
David Raynor (DRay)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-27-2024 09:57 PM
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?
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-28-2024 05:39 AM
Hi @enmiwong ,
That would be the dimension that differentiates each row. Similar to a group by.
What kind of filters would you like to implement? On the formula itself? on the widget? on the dashboard?
Always here to help,
Helena from QBeeQ
helena.ferreira@qbeeq.io
QBeeQ - Gold Implementation and Development Partner
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-30-2024 09:51 PM
{
"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
