Empty items related columns in parent table

[yolile]

command: spoonbill data/394282-adquisicion-cubiertas-maquinarias-vehiculos-utilitarios-annp-1-1621414358.json

file: https://contrataciones.gov.py/datos/api/v3/doc/ocds/record/ocds-03ad3f-394288-1

output:

1. correct tenders_items table

2. incorrect tenders_items columns in tenders table:

(this may be happening with all the arrays not only items)

[jpmckinney]

@yolile What specifically is incorrect? I think we have the logic that if there are <=5 items in a sub-array, then they are by default "rolled up" into the parent table.

[yolile]

Yes, but in this case, we have more than 5 items, so the tender_items table is created (correctly) but also all the items related columns are in the parent table (with empty values, incorrectly)

[yolile]

@sorenabell is this deployed and fixed in the web interface too?

[jpmckinney]

@sorenabell: @yolile tested and this seems to have been fixed in the CLI, but not yet deployed to the web?

[sorenabell]

Yes, let us test it a little bit more.

[sorenabell]

@yolile @jpmckinney This issue has been fixed in both CLI and web.

[jpmckinney]

Great :) So we can close the issue? @yolile can also test again.

[yolile]

I tested it again with the attached file and indeed, the problem is fixed for the parent arrays, however, there are some arrays at the item level that appear in both, the parent table (with empty values) and also as a separate table. See, for example "items/attributes":

• tenders table:

• tenders_items: (note here that there are also empty columns for the "additionalIdentifiers" array)

• tenders_items_class and tenders_items_attributes are correct:
  
  

files:
result.xlsx
394282-adquisicion-cubiertas-maquinarias-vehiculos-utilitarios-annp-1-1621414358.txt

[sorenabell]

@yolile a new version of the library has been released that fixes this issue.
Please test this fix on https://ocp12.open-contracting.org/

results 2021-09-22.xlsx

[yolile]

Hmm not sure if I'm doing something wrong, but I tried with this file again https://github.com/open-contracting/spoonbill/files/6858821/394282-adquisicion-cubiertas-maquinarias-vehiculos-utilitarios-annp-1-1621414358.txt and everything looks fine but the tender items. The resulted excel file has only 1 tender item, but the original JSON has 16, and the extra columns are still there in the tender sheet.
result (7).xlsx

[jpmckinney]

The resulted excel file has only 1 tender item, but the original JSON has 16

@yolile I'm not sure I follow. tender_items has 16 data rows. Spoonbill can't currently merge intermediate tables (i.e. tenders_items_class into tender_items) so there are also tables for tenders_items_class and tenders_items_attributes.

the extra columns are still there in the tender sheet

Yes, the only thing I notice in the attached Excel file is that tenders has many empty columns.

[yolile]

@yolile I'm not sure I follow. tender_items has 16 data rows.

Oh you are right, sorry, I had too many open Excel files and I was probably looking at the incorrect one when I wrote that. You are right, there are 16 items in the excel file, correctly. However, the tender_items sheet also has empty columns: /tender/items/additionalClassifications/0/* and /tender/items/attributes/0/*

[jpmckinney]

Hmm, are you sure you are testing your file on ocp12.open-contracting.org? I don't have any empty columns anywhere when I tested.

[yolile]

Yes:

test.mp4

(And I reproduced the issue about having only 1 item now) But if everything is working well for you, then maybe for some reason the problem is just for me and we can close this issue)

[jpmckinney]

Aha, at 0:50, I checked "Split arrays into separate tables", in which case everything is fine.

@sabahfromlondon What should be the default state of that toggle?

@yolile I'm able to reproduce empty columns on both the tenders and tenders_items sheets if I don't check that toggle
result-2.xlsx I don't know if this relates to #193.