Jump to content

Need CF to concatenate results from corresponding rows.


This topic is 2928 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi. I need help wrapping my brain around this problem.

Assuming the following result from a SQL query:

product1,A,B
product1,C,D
product2,U,V
product2,W,X
product2,Y,Z
product3,K,L

 

How would one go about transforming it into this using a CF?

product1,A,B,C,D
product2,U,V,W,X,Y,Z
product3,K,L

Here each product has only one row and all remaining field data for the other rows with the same product in the first result is concatenatied into this one row.

Or even into this ;)

{
  "label": "product1", 
  "data":
	[
		{"start": "A","end":"B"},
		{"start": "C","end":"D"}      				
	]
}, ...etc

This is actually the final JSON format I need.

Link to comment
Share on other sites

Thank you comment for your insight. Yes, it would indeed seem like an XY problem as you say, but there is a reason. If I can get the results of the SQL query (which is quite complex) procesed like I want, I can easily loop through the resulting rows and generate the JSON. So in this case I know Y. ;)

I only mentioned the final JSON result thinking that it might be useful for someone considering a solution to the concatination.

As this is only a small part of a large graph being plotted in D3.js in a WebViewer, I would like it to be done in memory for speed and "simplicity".

XSLT is an option, but I think the concatination can be solved with a CF. I see some related CFs over at briandunning.com, but nothing that fits. I hope someone with experience in this sort of thing can figure out something as these recursive CF things always give me an headache. :unsure:

 

Link to comment
Share on other sites

4 minutes ago, Andreas T. said:

XSLT is an option, but I think the concatination can be solved with a CF.

Sure it can - but if you are looking for speed and simplicity, you won't find either one going down this path.

Could you not do this as part of a script? Looping (iteration) is much easier to do in a script than in a CF that can only do recursion.

 

Link to comment
Share on other sites

Thank you for your advice. It is sometimes useful to get input on what not to do as well as how to do things.

It could be solved as a script, running a looping sub-script for the concatenations for each of the about 10 similar results that comes from the SQL queries. Each SQL query acts as a basis for a bar plot in the D3 graph. There could be 10 or more of those in addition to a few line plots. I was thinking that a looping script running a looping subscript 10 times would be slower than a CF being run 10 times. Maybe I am wrong about that. I would like the graph to update momentarily after the record is commited, but if it becomes too slow I will have to resort to a popup or something and generate on demand.

Link to comment
Share on other sites

18 minutes ago, Andreas T. said:

I was thinking that a looping script running a looping subscript 10 times would be slower than a CF being run 10 times.

I am not convinced that is so, but you could test this for yourself.

In any case, I believe the following custom function does what you want (I haven't tested it very thoroughly):

GroupItems ( listOfValues ) =

Let ( [
countValues = ValueCount ( listOfValues ) ;

item = GetValue ( listOfValues ; countValues ) ;
prevItem = GetValue ( listOfValues ; countValues - 1 ) ;

pos =  Position ( item ; "," ; 1 ; 1 ) ; 
id = Left ( item ; pos ) ;

cont = id = Left ( prevItem ; Position ( prevItem ; "," ; 1 ; 1 ) ) ;
out = If ( cont ; Right ( item ; Length ( item ) - pos ) ; item ) ;
sep = If ( cont ; "," ; ¶ )
] ;
If ( countValues > 1 ; GroupItems ( LeftValues ( listOfValues ; countValues - 1 ) ) & sep ) 
& 
out
)

---
P.S. Perhaps one of the resident SQL experts will be able to suggest a way to get this result directly from the SQL query - as I suspect it's not only possible, but also faster.

 

  • Like 1
Link to comment
Share on other sites

Indeed it does!

You are the man! (Sorry i didn't respond earlier, I did not get a notification)

This is exactly what I was looking for, thank you very much.

In fact, I tried to look into a SQL query that could yield this result, but I couldn't do it and after seeking advice in Stack Overflow, I was informed that it probably could not be done within FileMakers SQL framework. Here is the link to the post if you are interested.

Thanks again!

 

Link to comment
Share on other sites

This topic is 2928 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.