Ability to tranpose in Filemaker?

[_henry_]

Howdy all!

I would like to know if there is any way to do tranpose in FileMaker? Is it possible to do this? Any suggestions or methods are appreciated.

Thank you.

NB: I attached the Sample data that would be used for tranpose.

TIA.

SampleData.zip

[Colin Keefe]

You may want to look at a CF:

MatrixHoriz

Though I have a feeling you're after something a bit more complicated.

[_henry_]

Hi Colin,

Thank you for the MatrixHoriz information. Well, about your feeling for a bit more complicated, maybe that's true :-]

I have sample below:

12000837166     Hotel 1 90      804     236,257.18   

                                803     235,908.71   

                                802     209,366.05   



12016274339     Hotel 2 86      804     64,525.97                                                                                                                                                               

                                803     74,048.77                                                                                                                                                                

                                802     72,838.03                                                                                                                                                                

With MatrixHoriz, could it be implemented as below?

                                804             803           802

12000837166     Hotel 1	 236,257.18	235,908.71	209,366.05 

12016274339     Hotel 2      64,525.97 	74,048.77	 72,838.03

[Lee Smith]

Hi Colin,

Have you been able to get this to CF work?

The [color:blue] ITC_list__MatrixHoriz requires ITC_liste__GetListSeparators, which I could not find anywhere.

and The [color:blue] ITC_list__MatrixHoriz_loop , requires a parameter of counter. I suspect that this might be the Count (field) Function, but it isn't complete if it is?

Any how, I couldn't get it to work, but would love to see a working demo of it.

BTW, I went to Stefano Lesandrini, web site, but it is in a foreign language that I don't speak, maybe Italian, but there isn't any "Contact" email there that I could find.

Lee

[_henry_]

Hi Lee,

After reading his website, I thought I found this link to Contact him:

Contatti

However, I am not sure what this one for. But, you can choose "FileMaker" and press "Invia" (maybe Submit). Maybe FMers from Italy can confirm this.

[Lee Smith]

Thanks for the note. I sent an email to them inviting them to join our discussion here, or to send me an email.

Hopefully they will provide the information here.

Lee

[comment]

I'm confused regarding the real issue here. If the data is being imported, and you want to transpose it, you will need to create new records in another table - one that has fields for imported rows, and records for imported columns. No calculation is going to do that, you have to script this.

Furthermore, I am puzzled why would you want to transpose perfectly normalized data. I think all that's needed here is an auto-enter during import to fill-in the missing rows.

[comment]

FWIW, I have posted a TransposeArray() custom function here:

http://www.briandunning.com/cf/878

[Lee Smith]

_henry_, and comment,

I received an email last night from Stefano Lesandrini, the author of ITC_list__MatrixHoriz, and he has changed the original CF.

I made a demo file for those who are interested showing comment's and Stefano's CFs.

Hope it is helpful to the OP, and others.

Lee

_henry_.fp7.zip

[comment]

I'm not sure this is at all relevant to the original post, but as long as we are here:

To break a list of values into columns in 'across first' order is rather trivial and certainly does not require three custom functions.

ColumnsAcrossCF.fp7.zip

[_henry_]

Hi Michael,

...

Furthermore, I am puzzled why would you want to transpose perfectly normalized data.

Regarding your question, I would like to make a graph out of the database that has been tranposed. I am not sure if I can make the right graphics from "un-tranpose" data. Maybe you can give me some hint about it.

Also, thank you for giving me samples to it. I will look into and play with it. I'll get back to you if I found more difficulties. ;]

[comment]

This is somewhat confusing. Your sample data is in Excel, so I naturally assumed you wanted to import it. If you mean you want to use Excel to chart data exported from Filemaker, I believe it would be much easier to handle the entire operation in Excel.

[_henry_]

Sorry for the confusion. Here is my thought of process. First, I would import all the raw data and process it in Filemaker. Then, I will use processed data and I would tranpose it. After I tranpose it, I will create a graph from it. So, is it better using Excel to do the tranposing and creating graph?

[comment]

I think it's easier to let Excel do the transposing (or pivoting?), if you're doing the chart in Excel.

[_henry_]

... if you're doing the chart in Excel.

Can you do the chart in Filemaker? If yes, is there any sample on doing that?

[comment]

Not out of the box, but there is a plugin, several products that use the web-viewer and Brian Dunning's ChartMaker. Search the forums for "charts", and you're bound to find plenty of material.

[_henry_]

Hi Michael,

Thank you for the hint. I will look into it :(

[_henry_]

Hi Lee,

Thank you for the example. Sorry, when I read this thread couple days ago, I missed reading your post.

Thank you :P