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,

Parents
  • 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?

Reply
  • 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?

Children
  • I thought about that and provided instructions for the AND that you would have to add to the WHERE clause above. problem I saw was assembling the IN from a user report variable. As far as I know the pick list in a user report variable only allows picking one item so you would have to have a whole bunch of user report variables, assemble them into a properly formatted IN clause. Too hard jar for me. Else you would have to have the user manually type a properly formatted IN clause into a single user report variable. Too hard jar for the user. There may be a way, just didn't explore it enough. 

    Thought briefly about using a fence report that allows selecting multiple borings for a single sheet and exporting as csv text similar to above . Didn't explore but could eliminate some post processing.

  • Also,, I would verify that the method you showed above using iterate with a <<cr>> exports to excel as expected. As far as I can see that creates the illusion of multiple rows but us actually one row with 9 entries separated into 9 lines. It may import into excel as 9 items in a single cell. 

  • Have not tested this yet and likely won't be able to for a couple days.

    <<Iterate(_
      SID,1,9,,<<cr>>,<<Sql(_
        Select [LITHOLOGY].[Stratum] From [LITHOLOGY] Where [LITHOLOGY].[Stratum] = <<Get(SID)>> AND
        <<IIf(_
          <<Report Var.SelBors>> = "",True,<<Like(_
            <<Report Var.SelBors>>,"*,*",<<Like(_
              <<Report Var.SelBors>>,"*"[LITHOLOGY].[PointID]"*")>>,<<Like(_
                [LITHOLOGY].[PointID],<<Report Var.SelBors>>"*"
              )>>
            )>>
          )>>
        )>>

      )>>

    This uses the user report variable SelBors with these instructions

    "Leave blank = all borings in project, enter part of boring name (ie "B-" = all borings that start with that, enter comma delimited ", " list of boring names = only those borings."

  • Good point about the <<CR>> You are right, in fact when exporting to .XLSX it makes one row with 9 lines instead of  9 rows with one line each. I'm not sure how to get past this. Maybe use a comma delimiter and export to CSV?

  • Not sure I fully understand your expression but get the gist of what you are trying to do (probably because I have limited expertise in sql). If it works, it would not give the same functionality as a multi pick boring list. Further you still have to solve the 9 items in one cell problem. Using commas instead if <<cr>> would likely just place 9 comma separated items in one cell.

    I have a few ideas that I will try working on over the next several days. Let you know if anything pops up.