Joining two queries on multiple columns

[fooniquler]

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:

So I have something like that:

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:

and use multiple “join by field” transformations:

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:

So, any help would be appreciated.Thanks in advance!

[ebabeshko]

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

[yosiasz]

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

[fooniquler]

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.

[fooniquler]

Any ideas?

[yosiasz]

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

[fooniquler]

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:

So, my tables looks like:

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:

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