Help for query with included FLATTEN / GROUP BY (please)

Hello, I would like to use the Dataview functions GROUP BY and FLATTEN, but I just can’t seem to get it right, and I need some support to understand where I’m making mistakes.

This is my dataview file metadata-structures :

data: 2024-02-04
id_activity: 20240204105440
activity: Prova 2
data_start: 2024-02-04
activity_impact: 10
activity_impact_progress: 1
progress: 10
note: prova prova prova
customer: XXX
logo_customer: Logo-XXX.png
project_name: xx_Marginalità-comm-2024
img_product: ZZZ020492.png
pn_plant: ZZZ020492
production_plant: XXX
type: special-project

And this is my query :

TABLE WITHOUT ID  customer as "Customer", 
embed(link(logo_customer,"80")) as "Logo", 
link(, pn_plant) as "Agrati PN" ,  
embed(link(img_product,"50")) as "Draft", 
activity as "Activity",
activity_impact as "Tot. Impact(€)", 
activity_impact_progress as "Imp. Status(€)",
"<progress value='" + round(progress) + "' max='100'></progress> " + round(progress) + "%" AS Progress
FROM "001-Agrati_PRM/Progetti_Speciali/Marginalità_xxx2024"
where type = "special-project" and project_name = "xxx_Marginalità-comm-2024"
sort customer
FLATTEN customer
GROUP BY customer

This is the result of my query (not working ):

My expected outcome should be as follows (something else…) :

Thank you for your help.

Exactly why do you want to GROUP BY and/or FLATTEN in your query?

FLATTEN is used either to compute a field and store in a new variable, or to split a list into separate rows. So FLATTEN customer where customer always only contains one customer doesn’t make sense. It would make sense to do FLATTEN customers as customer in case it was a list of customers associated with that one note. (And please don’t do just FLATTEN something as it hides what something refers to, whether it’s before or after the flattening.

GROUP BY on the other hand is used to group similar rows into one row. If we just focus on three variables; customer and activity and data_start, after the GROUP BY customer you get the following variables to play around with:

  • key – which matches whatever the unique values of whatever you grouped by (in this case a customer)
  • rows.activity – A list of all the activities of a given customer
  • rows.data_start- A list of all the activities of a given customer

One vital thing to remember though, is that due to the grouping all the variables moved into rows are kind of disconnected, so it’s not easy to do stuff like data_start - data_end, as that would become rows.data_start - rows.data_end which I’m not sure would give valid results, as you’re subtracting a list from another list.

Lastly, and sadly, dataview can’t do rowspans so although doing the above changes, of making activity into rows.activity and so, would make it present a list of those activities, it wouldn’t correspond horisontally with the other lists since they’re disconnected. So I believe what you’d rather want to do, if I understood your use case correctly, is simply to use something like SORT customer ASC, pn_plant ASC to sort first on the customer and then on that pn_plant. You should also remove the FLATTEN and GROUP BY lines. This would allow for you list to focus on one customer at a time, and still allow for those calculations you’ve done to be as you expect them to be.

you’re right. My use case would be to have only one customer entry (and its logo) with multiple activities and their respective information: (start_date / end_date / impact / impact status / progress). What you’re suggesting is a table sorted by customer and code. My idea was based on the hope of making the table understandable and visually appealing, and GROUP BY achieved both.
However, I tried with the following code:

TABLE without ID embed(link(logo_customer,"100")) as "Logo",link(,rows.pn_plant) as "XXX PN" ,embed(link(rows.img_product,"50")) as "Draft",rows.activity,rows.data_start,
"<progress value='" + round(rows.progress) + "' max='100'></row.progress> " + round(rows.progress) + "%" AS Progress
from "Varie/Test/example-byg"
where project_name="XXX_Marginalità-comm-2024"
sort customer asc, pn_plant asc
GROUP BY logo_customer

In English: "Everything is misaligned as you mentioned. I suppose this is the result of the rows.xxxx. Is there no way to work around the issue?
For example, without using ‘rows.’ and the lists it generates… My goal is to have 1 customer and their activities, data start, data end, etc. aligned and calculated for each individual entry consistently, like this :

Sadly, you’ve got to pick either one currently. Either you’ve got many rows with the same customers but the other fields are correct, or you get one row for each customer with all the other fields misaligned…

Okay, I understand (unfortunately). One last favor, could you give me a hand to fix the Progress bar? I can’t get it to appear on every line, and I can’t figure out what I need to do. See below, this is the query :

TABLE without ID embed(link(logo_customer,"100")) as "Logo",link(,rows.pn_plant) as "Agrati PN" ,
embed(link(rows.img_product,"50")) as "Draft", 
rows.activity as "Activity",
rows.activity_impact AS "Prog. impact",
rows.activity_impact_progress AS "Imp. Status(€)",
"<progress value='" + round(rows.progress) + "' max='100'></progress> " + round(rows.progress) + "%" AS Progress
FROM "001-Agrati_PRM/Progetti_Speciali/Marginalità_CVB2024"
where type = "special-project" and project_name = "CVB_Marginalità-comm-2024"
SORT customer ASC, pn_plant ASC 
GROUP BY logo_customer

and this is the result :

Thank you !

Try temporarily adding the value you use to calculate the progress. One or more of those values are most likely zero or not defined. Or if its multiple values, you might need to do sum(rows.progress) or something similar (maybe average(rows.progress)?)

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.