(New) InfoAssist User: How can I create a Virtual Field using Detail Define/Summary Field with multiple joins; that will not compute the entire dataset of the fields selected?

[Joshua Mack]

Hello, everyone! I am new to report building in WebFocus and currently in lieu of obtaining my 1st certification in SQL. I am currently building a consolidation report for the company I work for using WebFocus | InfoAssist not certain of the verison. I don't have administrative access to manipulate the data in syntax, so I'm attempting to do this by InfoAssist Only and I've reached an impasse in my skill set.

Hopefully, the attachment and explanation will suffice, in my effort to get support.

This consolidation report is designed to easily identify opportunities to consolidate locations with EXACT items into one location; decreasing the storage capacity levels, improving inventory accuracy and host of other benefits.

This report design, required three tables to be joined and the fields needed are listed below

• Inventory
  • Item #, UPC, Description, Lot #, Location & On-Hand Quantity
• Item Dimensions
  • Unit of Measures and Gross Cubes
• Location Dimensions
  • Location Character Group, Location Dimensions Groups & Usable Cubic Dimensions

    

The issue I am having with the Summary Field (Compute) UI and the Detail Define (Compute) is, the fields that I need for the virtual field both have multiple rows of records formatted in PACKED. How would I go buy filtering this data to return the correct calculations? (Note: After creating this in Detail Define, the system says that the file is unrecognized but the Summary field will return a number but not the correct one.) If you look at the attached photo I have a field called CAPBYITEM = (Usable Cubic Dimensions / Gross Cubes), which is designed to return the max location capacity by item but in order to return the correct calculations the formula should be as the following.

CAPBYITEM = (Usable Cubic Dimensions by Location Dimensions Group) / (Gross Cubes by Unit of Measure = EACHES)

• Locations relational tables = (Locations + Location Character Group + Location Dimensions Group + Usable Cubic Dimensions)
• Items relational tables = (Items + Unit of Measure [Eaches, Inner Packs & Cases] + Gross Cubes)
  • Eaches, Inner Packs and Cases all have different dimensions for the same item

More screenshots to support answering this question.

 

[Martin Yergeau]

In case that you don't know, a DEFINE is applied to each data set and a COMPUTE to the filtered/oordered data set.

In other words, a DEFINE is applied to each data row of the TABLE FILE where a COMPUTE is applied only to data row of the TABLE FILE once the WHERE clause and the BY field have been "applied".

You cant see a DEFINE as a way to "add" a virtual field that doesn't exist in the source table.

You can perform a WHERE on a DEFINEd and/or COMPUTEd field.

You can perform a COMPUTE on a DEFINEd field.

As for "the system says that the file is unrecognized" you may want to look at the execution log/trace to figure where and why the error.

You can execute your fex with Trace on / Debug on. This option should display from execution button.

A good option to have your request working as you whish is to first JOIN, create the DEFINEd fields and filter (WHERE) the sources tables to then HOLD the result in a new file which then be used to do the calculation.

I strongly suggest that you go step by step.

First, have your raw data.

Second, manipulate the raw data to have the calculation you are looking for.

[Joshua Mack]

Thank you for your reply!

I have attempted to filter the computed field that I have created using the Summary Field and there is no filter option to apply "where" to. Any other suggestions?

[Martin Yergeau]

If I remember well, you must drag it into the Filter section. Or open the filter section and then add the COMPUTEd field.

Also look what are the options in the "More" menu of the right click from the COMPUTEd field.

[Joshua Mack]

OK. Let me start over and see if I am able to do as you've suggested. To clarify, I should perform the join 1st, create a define that is filtered by the exact data I need, then place that define in a HOLD file? Then I am able use those files to perform the computation? BRB to attempt.

[Martin Yergeau]

You place ALL source fields that you need in the HOLD file and filter the data as much as you can at first step to limit the number of returned rows. You can also add the virtual fields (DEFINEs) at that step.

After that step, the goal is to work with a single file to perform the more complex calculation and logic.

Working only with desired data set is easier than several table and thousand of rows

[Joshua Mack]

Martin,

What format should I be using when creating the hold file? This is my 1st time creating a hold file.

[Martin Yergeau]

Depend on how much hold data you expect to have.

Generally, I use FOCUS but you will be limited to 2M rows if I remember well which is normally enough.

Advantage to use FOCUS file is that you can create an index on it where you can't on a text (.ftm) or binary file (.ftm).

Also a FOCUS file will generates a .mas (master file) that contains the hold file definition.

[Joshua Mack]

Martin,

After following your instructions this is what I have created in my HOLD file formatted as FOCUS. This is the exact data I would need to retrieve the correct calculations I needed. How do I go about adding this file to my pre-existing report? JOIN?

[Martin Yergeau]

You should not add it to the pre-existing report but start a new report using that HOLD file as the source.

Once you have ran the fex where the output is the FOCUS file, (it's been a while I have not used InfoAssist) you should be able to create another report and the HOLD file should be visible as an available source file. You must select it.

To be able to use it in the pre-existant report, the HOLD file must be physically hold on a physical app path which it doesn't the way you did : it's in "memory" and not available outside the execution. But holding it physically may have several complication to manage that you don't want with the WebFOCUS knowledge you have. Keep it in "memory".

[Martin Yergeau]

Have a look into this manual (it's the latest one, so may not be the proper one for your release).

Chapter 11 is about HOLD files.

TIBCO WebFOCUS® InfoAssist User's Manual Release 8207 and higher

[Joshua Mack]

Oh wow, I was under the impression that I was able to create these hold files and add them to the pre-exisiting report. Since the two hold files I need to build are from two separate tables that are connected at JOIN.

How can I create those two HOLD files and merged them to be used simultaneously in the report I am looking to build?

I apologize if I am pestering you, I'm a student looking to learn as much as I can.

[Martin Yergeau]

You could but will need little more knowledge. See documentation that I attached in previous post.

But to simplify, you can JOIN the real tables, filter, create the DEFINEs and then HOLD that result.

[Joshua Mack]

I want to say thank you for all of your support, I was able to build the report and make it accessible to my team! Thank you very very much!

[Martin Yergeau]

Happy to hear that.

Was a pleasure to help