Joining two queries on multiple columns

Hi!
I am using Dynamic Text Plugin for visualization and Infinity Plugin as datasources.
My first query gets a list of payments by sending request with body like: "method": "Sale". After that, I group the fields using transformations:
image
So I have something like that:
image
Now I need to make one more query to the exact same endpoint with body like: "method": ["Sale", "ReturnSale"] so the “sum” column of query2 may differ from “sum” column of query1. So, my goal is join two queries on multiple columns. I tried to group fields from query2:
image
and use multiple “join by field” transformations:
image
but as I understand it, only the first transformation is applied. In query inspector, I see that after applying all the transformations I get something like this:


But I want to get:
image
So, any help would be appreciated.Thanks in advance!

Probably not the most elegant solution, but you can hide extra columns using Organize fields by name transformation:

please post a sampling of both data coming back from your data sources not as an image but as usable text data as json. you could probably use jsonata to join the two

Sure! First response:

{
  "success": true,
  "result": [
    {
      "id": 4648,
      "sum": 10000,
      "organisation": "org1",
      "type_title": "type1",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4651,
      "sum": 100,
      "organisation": "org1",
      "type_title": "type1",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4652,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type1",
      "printer_address": "address2",
      "method": "Sale"
    },
    {
      "id": 4653,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "Sale"
    }
  ],
  "error": null
}

Second response:

{
  "success": true,
  "result": [
    {
      "id": 4648,
      "sum": 10000,
      "organisation": "org1",
      "type_title": "type1",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4651,
      "sum": 100,
      "organisation": "org1",
      "type_title": "type1",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4652,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type1",
      "printer_address": "address2",
      "method": "Sale"
    },
    {
      "id": 4653,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4654,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "ReturnSale"
    },
    {
      "id": 4655,
      "sum": 200,
      "organisation": "org2",
      "type_title": "type3",
      "printer_address": "address1",
      "method": "ReturnSale"
    }
  ],
  "error": null
}

So, as you can see, the second response exactly contains the first response and possibly additional data. In this case, I need to get a table:


In second response we have:

{
      "id": 4653,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4654,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "ReturnSale"
    }

Exact same printer_address, organisation and type_title, but different method, so we summarize the sum value and put it in appropriate cell (highlighted in green in image).
Also, in the second response we have:

{
      "id": 4655,
      "sum": 200,
      "organisation": "org2",
      "type_title": "type3",
      "printer_address": "address1",
      "method": "ReturnSale"
    }

We don’t have address1, org2 and type3 data from the first response, so it should be ignored.
I hope this brought some clarity.

Any ideas?

more clarity but also new confusion. you have 2 distinctly different Second responsee

  1. I am sending a request with a body: "method": "Sale" and get:
{
  "success": true,
  "result": [
    {
      "id": 4648,
      "sum": 10000,
      "organisation": "org1",
      "type_title": "type1",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4652,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type1",
      "printer_address": "address2",
      "method": "Sale"
    }
  ],
  "error": null
}

Then I group by:
image
So, my tables looks like:
image
2) I am sending a request with a body: "method": ["Sale", "ReturnSale"] and get:

{
  "success": true,
  "result": [
    {
      "id": 4648,
      "sum": 10000,
      "organisation": "org1",
      "type_title": "type1",
      "printer_address": "address1",
      "method": "Sale"
    },
    {
      "id": 4652,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type1",
      "printer_address": "address2",
      "method": "Sale"
    },
    {
      "id": 4654,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "ReturnSale"
    }
  ],
  "error": null
}

It contains the first response and additional data:

{
      "id": 4654,
      "sum": 100,
      "organisation": "org2",
      "type_title": "type2",
      "printer_address": "address1",
      "method": "ReturnSale"
    }

If I group by it in the same way as in the first response, then I will get table:
image
3) Now I need to join two tables on “printer_address”, “organisation” and “type_title” to get:


So, the only difference between first and second responses is additional data that may change “sum” values.
The problem, as far as I understand it, is in the “join by field” transformation: I can’t make a join first on “printer_address”, then on “organisation” and then on “type_title” - only the first transformation is applied