Hello!
I do have a query that gives me data like this:
I would like if possible to bind this data to my tree view but I don´t know where to bind it. Should I use a change script?
SOLICITANTE would be the first folder level,
DESCRIPCION should be the child level, and
NUMERO PETICION the grandchild level
Tree data doesn't necessarily display well in a database format.
You can populate most if not all controls by building the json in a script transform. You could put a binding on your the "items" property that triggers when your data changes. Then you put a script transform on that that builds the json for your tree.
You can copy the items property and paste it into a text editor to get an idea what the json looks like. That's usually my first step if I'm building the json for something in a script transform because it's quicker than looking up documentation (for me).
You may want to consider organizing your data based on the order it is used. If you're in MS SQL, consider using a recursive CTE and consider putting it in a stored procedure to compile the execution plan. I'm told that named queries also compile the execution plan but I haven't benchmarked that to know if it's the same thing that happens when you declare a stored procedure. I typically use stored procedures for anything that could have a significant execution time.
Thank you Steve, that´s the point, convert the data into a Json.
Bind the query to the props.items of the Tree
Then used Transform Script using Jython on the query and organizing the hierarchy like:
def transform(self, value, quality, timestamp):
raw_data = value
if raw_data is None or raw_data.getRowCount() == 0:
return []
# Initialize a dictionary to hold the hierarchical data by SOLICITANTE
hierarchy = {}
# Iterate through each row in the dataset
for row in range(raw_data.getRowCount()):
solicitante = raw_data.getValueAt(row, 2) # SOLICITANTE at index 2
descripcion = raw_data.getValueAt(row, 0) # DESCRIPCION at index 0
numero_peticiones = raw_data.getValueAt(row, 1) # NUMERO_PETICIONES at index 1
# If the SOLICITANTE (parent) is not in the hierarchy yet, add it
if solicitante not in hierarchy:
hierarchy[solicitante] = {
"label": solicitante,
"expanded": False,
"items": [] # This will hold DESCRIPCION children
}
# Add DESCRIPCION (child) with NUMERO_PETICIONES (grandchild)
descripcion_item = {
"label": descripcion,
"expanded": False,
"items": [
{
"label": "NUMERO PETICIONES: {}".format(numero_peticiones),
"expanded": False
}
]
}
# Append the DESCRIPCION item to the SOLICITANTE's items
hierarchy[solicitante]["items"].append(descripcion_item)
# Convert the hierarchy dictionary to a list of dictionaries
json_list = list(hierarchy.values())
return json_list
Result:
I guess would be similar aproach to populate bar charts from querys.
A lot of times you can set the return format to json. Play around with return format and see if it's what you are looking for.
Thanks Steve, I trying this out for a charts and is working great! I was using datasets and going crazy because didn´t work.
Just what i was looking for!
a
I assure you I have had the same problem in the past. Glad I could help.
I use something like this to build this kind of tree:
tree = []
for row in data:
folder = tree
for val in [row['SOLICITANTE'], row['DESCRIPCION']]:
try:
folder = next(f['items'] for f in folder if f['label'] == val)
except StopIteration:
folder.append(
{
'label': val,
'expanded': False,
'items': []
}
)
folder = folder[-1]['items']
folder.append(
{
'label': "NUMERO PETICIONES: {}".format(row['NUMERO PETICION']),
'expanded': False
}
)
Maybe it helps, maybe it doesn't. Do with that what you want.
Yes, this code to build trees works great, it´s dynamic no matter the deep of the hierarchy and sure faster.
Thanks Pascal!
Your code:
the code I was using:
Now I will have to sum them and show one unique result, but that´s another story.
What do you mean, sum them ?
Do you want to sum the "numero peticiones" ?
yep, I think I will have to do that to show the totals
For example, here:
You'd want to have
AREAS
|-- CON_ASTRAS
|-- NUMERO PETICIONES: 3
That's it ?
Yes
Do that in the query then.
group by SOLICITANTE and DESCRIPCION and select sum(numero peticion)
Ok, got a solution, thanks Pascal
def transform(self, value, quality, timestamp):
raw_data = value
if raw_data is None or raw_data.getRowCount() == 0:
return [] # Return an empty list if no data is available
tree = []
for row in range(raw_data.getRowCount()):
solicitante = raw_data.getValueAt(row, 2)
descripcion = raw_data.getValueAt(row, 0)
numero_peticiones = raw_data.getValueAt(row, 1)
folder = tree
for val in [solicitante, descripcion]:
try:
folder = next(f['items'] for f in folder if f['label'] == val)
except StopIteration:
folder.append(
{
'label': val,
'expanded': False,
'items': []
}
)
folder = folder[-1]['items']
try:
existing_peticion = next(item for item in folder if "NUMERO PETICIONES" in item['label'])
existing_peticion['label'] = "NUMERO PETICIONES: {}".format(
float(existing_peticion['label'].split(": ")[1]) + numero_peticiones
)
except StopIteration:
folder.append(
{
'label': "NUMERO PETICIONES: {}".format(numero_peticiones),
'expanded': False
}
)
return tree
Why floats ? Do you ever get fractional "peticiones" ? Didn't look like it.
Also, I'll insist: Do it in the query.