Text Tables: Statistics For a Non Key Field

Question

Within the LITHOLOGY table, there is a field .Stratum where each row in LITHOLOGY can be assigned a .Stratum integer 1-9. Suppose they are not necessarily in depth order (interlayered) and several LITHOLOGY rows within a POINT might be assigned the same stratum number. 
 
 I want to create a text table that contains rows .Stratum 1 through 9 and then in columns evaluations of the project data by stratum (rather than by boring). Column headers such as "Stratum ID", "min. depth stratum was encountered", "list of graphics used in this stratum", etc. However, .Stratum is not a set key by which I can dictate the table. How should I make the data expressions so that the table filters and outputs the way I want? 
 ie. For key set PointID, Header Text "Stratum ID", I want to output a row for each stratum encountered in the project. 
 
 Thanks,

szang · Accepted Answer

One method to do this is as follows. The example uses a Table called LITHOLOGY with fields for Depth, Bottom, and Strata_ID. The example creates the statistics for the entire project, it can be modified to create the statistics for selected borings though it is not as clean and straightforward. Create a text document report (not a text table) with a keyset of project. The following code was placed in the text document report Stratum,Min Top Depth,Max Top Depth, Min Thickness,Max Thickness Strata 1,<>,<>,<>,<> Strata 2,<>,<>,<>,<> Strata 3,<>,<>,<>,<> Strata 4,<>,<>,<>,<> Strata 5,<>,<>,<>,<> Strata 6,<>,<>,<>,<> Strata 7,<>,<>,<>,<> Strata 8,<>,<>,<>,<> Strata 9,<>,<>,<>,<> This will create a comma delimited text containing headers and 9 lines of comma delimited output. SQL expressions are used to retrieve the desired data. On my example project, this produced the following output. I exported this to a txt file and opened it in excel as a comma delimited file. This produced the excel file below. You can add additional SQL expressions to each of the 9 lines to extract the information (averages, counts, etc.) you desire. To modify this so that you can select which borings from the project you want to summarize you would need to take the following steps. 1. Change the report keyset to PontID. 2. Insert a line above the previous text in the report that has the data item <> this is required because of the change in the keyset and will also generate a separate text report for each boring selected. more on this later. 3. You will have to add an "AND" clause to the WHERE clause of each sql expression in the report that further restricts the selected items to the borings selected. This would be something like AND [LITHOLOGY].[PointID] IN (<>) 4. As noted above this would produce an output page for each boring selected but the data on each page will be the same and represents the correct summary of the desired borings. 5. To import this in excel, you can import the whole file and then cut and paste or delete the repetitious data. You can also edit the text file prior to import. There is probably a way to only output the first page of the report but I can't think of it right now. If you would like more information or an example of how to do this please let me know.

szang · Answer

Ok, I explored 3 options to incorporate boring selection into the output statistics. The first 2 didn't work but the third does. It is similar to the text document solution above but I will describe fully below: The example uses data in a LITHOLOGY table that includes depth, bottom and Strat_ID fields (among others). The test data is shown in the picture below. It can be used to manually check the accuracy of the solution. I created a text document report with Key set PointID (note this is different than the previous solution above). Using Point ID key set gives me access to the standard gint boring selection pick list and all filters at output time. The following Code was placed in the text document report. <>=<>,", ",1)>>,"""Summary for borings <>""")>> <>=<>,", ",1)>>,"Stratum,Min Top Depth,Max Top Depth,Min Thickness,Max Thickness,No. of Occurences")>> <>=<>,", ",1)>>,_ "Stratum 1,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 2,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 3,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 4,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 5,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 6,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 7,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 8,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> <>=<>,", ",1)>>,_ "Stratum 9,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>,_ <>))>>"_ )>> Notes on this code: A. Line 1-the triple quotes are required to generate a quoted text string that includes all the borings. This forces it to import into a single cell in excel rater than one cell for each boring and serves as a title for the summary table. B. Line 1- the if statement compares the current Point to the first point in the output list and only prints something if it is the first one in the list. All other points generate nothing which eliminates the repetitive data. Note that the delimiter in the <> data item is a comma followed by a space. This same if statement is used for all the rest of the outputs on this page. Thus it only generates data for the first selected point. C. Line 2- This prints titles for each column in the table. Quotes are required here or gINT will interpret the commas as additional arguments in the if statement. D. Line 3-10 - The if statement only prints this line of data if the PointID is the first item in the output list. Quotes are required around the True clause of the if to prevent gINT from interpreting the comma delimiters as argument separators. This same format is repeated for each stratum ID 1 through 9. E. Line 5-9 - The sql statements include an AND clause that further restricts the output to the selected borings as represented by the data item <> F. Line 11 to end - Each block repeats lines 3-10 but increments the strata_ID by 1. You could probably do this more efficiently using an iterate function which I tried to do. I had trouble getting the quotes in the right places so resorted to the brute force method. Note that the strata_ID number in my data base is a text data type rather than a number so I had to quote the number in the sql statement. Previewing this output on the screen and leaving the boring selection blank selects all borings in the project and produces the following: The is the same result as obtained previously using the text document with a project keyset (see answer above). Exporting this to a text file from gINT and opening in excel gives the following summary. Importing into excel is as simple as Starting excel, selecting file open, selecting text file type in the file dialog, selecting your exported file, selecting delimited in the import wizard, selecting comma as a delimiter and there it is. No post processing required. I suppose you could modify the code to use tab delimiters which would eliminate one step in the import process but I did not try that. Reexporting selecting only 4 borings in the output screen and reimporting into excel gives the following output. This can be checked manually using the data sample provided at the beginning of this post thus it seems to work fine. It should work the same if borings were selected using gINT's output filters as well but I haven't tried that. As usual I have not tested this fully with lots of different data so I may be missing something. There are probably better ways to do this but this is one way. You can add additional sql statements to each strata block to extract additional data as desired, just make sure they are separated by a comma delimiter and and are within the quotes of the iff function. In my example you have to add the statements repetitively to each of the 9 blocks (if you can figure out the iterate function this would make changes easier).

Text Tables: Statistics For a Non Key Field

Top Replies