Text Tables: Statistics For a Non Key Field

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,

  • "<<Case(" maybe?. Then I would have to define output parameters for the case of each stratum 1 through 9 for LITHOLOGY entries that were each assigned stratums 1 through 9. Sounds rigorous but it might work. Any better ideas?

  • Just to clarify before I head down the wrong path. You want to output a table that has 9 and only 9 rows labeled stratum 1, stratum 2, etc. Columns would be min depth, max depth, min thickness, max thickness, etc. These outputs would be based on all borings selected at output time. 

    Is there any reason you need to use a text table?

    Not saying I know how to do it, just dont want to waste my time doing something you dont want. It would be helpful if you posted an example table that shows what you are expecting (create it in word and export as a jpg to post here, doesn't have to include real data, just the type of output you expect)

  • The stratum field entry is limited to integers 1-9, so those row only will be fine, or only rows for strata are actually used  is fine, also.

    I have the rest of my data displays (except logs and fences) in text-table format because they are easy to export to Excel for use in my company's reports, so it made the most sense for me to use a text-table for this.

    Your written interpretation of what I would like to display is correct, "rows labeled stratum 1, stratum 2, etc. Columns would be min depth, max depth, min thickness, max thickness, etc. These outputs would be based on all borings selected at output time."

    For the first column I have been trying a bunch of SQL statements so far, but no luck. Hopefully I am at least on the right track.

    <<SQL(Select [LITHOLOGY].[Stratum] From [LITHOLOGY])>>

    <<SQL(Select Distinct [LITHOLOGY].[Stratum] From [LITHOLOGY])>>

    <<Iterate(ID,1,9,,,<<SQL(Select [LITHOLOGY].[Stratum] From [LITHOLOGY] Where [LITHOLOGY].[Stratum] = <<Get(ID)>>)>>)>>

  • 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,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "1")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "1")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "1")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "1")>>
    Strata 2,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "2")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "2")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "2")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "2")>>
    Strata 3,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "3")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "3")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "3")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "3")>>
    Strata 4,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "4")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "4")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "4")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "4")>>
    Strata 5,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "5")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "5")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "5")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "5")>>
    Strata 6,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "6")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "6")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "6")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "6")>>
    Strata 7,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "7")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "7")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "7")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "7")>>
    Strata 8,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "8")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "8")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "8")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "8")>>
    Strata 9,<<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "9")>>,<<Sql(Select max([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "9")>>,<<Sql(Select min([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "9")>>,<<Sql(Select max([LITHOLOGY].[Bottom]-[LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[Strata_ID] = "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 <<LITHOLOGY.PointID>> 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 (<<OutputKeysInClause>>)

    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.

    Answer Verified By: Jason Varounis 

  • Agree that works. However, that is a great deal of post-processing to ask a user to do, especially one who may not be proficient in gINT's language or programming in general. I would like to have something that is simpler to prepare; also why I started with a text table.

    I think I am getting closer. I changed the text table keyset to 'project', then tried this for the first column:

    <<Iterate(ID,1,9,,<<cr>>,<<Sql(Select [LITHOLOGY].[Stratum] From [LITHOLOGY] Where [LITHOLOGY].[Stratum] = <<Get(ID)>>)>>)>>

    The iterate function "effectively" changes the keyset of the statement to ID.

    This works, and I can tailor the expression for depths, graphics used, or whatever fields I need to query. Limitation is that there is no way to select borings because of the keyset 'project'. This table can only be applied project-wide. If I change the text table keyset to PointID the output error's out.

    I think I can modify this statement with an additional WHERE clause to apply only to certain borings, those borings specified in a 'user report variable'. I can populate a lookup table of all the borings in the project and have user select their borings from there, then I can keep the keyset as 'project'. What do you think about that?