Need CF to concatenate results from corresponding rows.

[Andreas T.]

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.

[comment]

This seems to be an XY problem. If - as I suspect - you want to end up with a JSON file, I would suggest you start with an export, not with a SQL query. You could export as XML and use XSLT to format the exported file as JSON.

 

 

Edited March 22, 2016 by comment

[Andreas T.]

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.

 

[comment]

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.

 

[Andreas T.]

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.

[comment]

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.

 

[Andreas T.]

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!