0

Using the IMPORTXML function on Google Sheets, I've got two XML data sets at two URLs, respectively in cells A1 and A2. I've got the XPATH query in cell H1. I would like to run a single query on the combined XML datasets (i.e., the combined result of XML data from URLs in A1 and A2.

As an example, [this URL][1] is in cell A1 and [this second URL][2] is in cell A2. The xpath in cell H1 is (//*[local-name() = 'NEW_DATE'])[last() - position() = 25]/../*[local-name()! = 'Id'].

I've tried the below formula, which returns two query results, one for each URL where the XML query in cell H1 contains [last() - position() = 11] or #N/A error for the first URL and the 25th row for the second URL where the XML query is as above, i.e., [last() - position() = 25]:

=QUERY({IMPORTXML(A1,H1);IMPORTXML(A2,H1)})

Note that in this sample, the first URL has 13 results as of the date of this question (hence retreiving row 11 when the XML query includes [last() - position() = 11] works but not when the XML query includes [last() - position() = 25] because there is no 25th row in that URL's XML data). Combining XML results of the first and second URLs, I would like to retrieve the 12th row in the second URL, which would be the 25th row if both URLs are combined.

This user's question is similar though the OP seems to run a common XPATH query on multiple, uncombined URL sources.

I would like to run a single query after the URLs are combined into a single dataset.

Is this feasible?

[1]: http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=year(NEW_DATE) eq 2022 [2]: http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=year(NEW_DATE) eq 2021

0

1 Answer 1

1

Combine the xml data by using an array

  • Enter this formula in Cell A4
    ={IMPORTXML($B$1,$B$2);IMPORTXML($D$1,$D$2)}

  • Then transpose every 14th set of rows using this formula
    =ArrayFormula(split(transpose(split(regexreplace(query(array_constrain(if(mod(row(A4:A), 14) = 4, "^", "|") & A4:A,max(row(A4:A) * (A4:A <> "")),1),,9^99), "^\^",), "^", true, false)), "|", true, false))

How to adapt the formula to your data

The formula (as written) assumes that the IMPORTXML data commences on row#4 but this may not be true in every case.

In order to adapt the formula to the location of your data, the key element is:

  • if(mod(row(A4:A), 14) = 4, In this case, the mod of the first row of data (row#4) divided by 14 (the number of rows in each set of data) = 4.

To adapt the formula:

  • identify the row number for the first row of IMPORTXML data array
  • calculate the MOD where the dividend = the starting row#, and the divisor = 14
  • substitute your calculated mod for '4' in the if statement.

Your screen should look something like this. This snapshot is cropped; the data for each day extends across to column P enter image description here

Hat-tip: Ed on Google Support: Transpose from column to rows, every nth rows

2
  • The formulas worked with a new sheet but when I transferred them to an existing sheet and made adjustments for cell and range references, it didn't work any longer. I think it may have to do with the second formula's ARRAY_CONSTRAIN function and / OR functions nested inside it, such as MOD. Can you share your sheet or explain what's happening in the above functions? Thanks.
    – Catalyx
    Jan 23, 2022 at 15:58
  • The key is MOD. I can't explain why your formula would work with a new sheet but not with the original sheet (assuming you are using the exact same data set) . Anyway, I just added an explanation of how to adapt the formula to your data.
    – Tedinoz
    Jan 24, 2022 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.