Good morning and thanks for taking the time to look this over. I am currently hitting a slight roadblock on a report and was wondering if anyone had a solution. Basically what I need to do is create a field that combines all of the distinct alpha values within three other fields. I haven't been able to locate a function built for this specific sort of thing, but I am sure there is a string of logic that could work. Please note that this will be created using InfoAssist, so I'd like to stay out of text editor unless absolutely necessary.

Below is a simplified example of what I am looking to do. I have three fields from the same table with different sets of values. Some of the values overlap across sets. What I am looking to create is a field containing a set of all distinct values from all three native fields.

Field1:
Blue
Orange
Green
Red

Field 2:
Red
Yellow
Brown

Field 3:
Green
Black
Orange

Field I am looking to create:
Blue
Orange
Green
Red
Yellow
Brown
Black


I have already looked around for a thread on this exact subject and have been unsuccessful in finding one, but if anyone has a link to a solution, I would greatly appreciate it. Thank you in advance for any help you guys can provide.

I have a solution, there is always a solution.

But it will require work outside of InfoAssist (I think, as I have never used it)

Evan,

Welcome to Focal Point!

There are aggregation functions that can be applied to fields. One is Count Distinct.
Please review the following image for the function location:


You can also create Summary (Compute) fields that will contain the count distinct values for further computations. In the Summary Field (COMPUTE) window add the prefix CNT.DST. to the field.

There are InfoAssist instructional videos located on the Technical Support Center under the heading "Get Answers Now" click on Technical Video Center.

Hope this points you in the right direction to creating your report.

Thank you for participating in the Focal Point Forum!
Tamra Colangelo
Focal Point Moderator
Information Builders

Thank you for the response, Tamra. Unfortunately it doesn't answer my question from what I can tell. Maybe I can clear up what I am looking for a little better through further explanation.

Within the report I believe that the field I am having trouble creating will be a By field. While I listed three color fields in my primary example, my fields are actually a set of codes - primary codes, secondary codes, and remaining codes. Every item has a primary code, some have a secondary, and those with both a primary and secondary may have a remaining code if necessary. Each code has a measurement associated with it. The issue that I am running into here is that I want to create one field that contains a distinct list of all codes and another field that sums all measurements associated with that code. Now, I have already completed the measurement field and it is working as expected, but I have yet to find a way to create a field that contains all values within the three fields together (primary, secondary, and remaining).

Thank you for your time and I appreciate the help.

Evan,

Possibly, providing the code that you are using to create your measure field to give us an idea of what you are already doing. This might help with figuring out how to create a field for the distinct values.

A response will be in terms of using the InfoAssist GUI tool.

A HOLD file might be required to get the data in prepared using computed fields and then create a report off the HOLD file.

To access the code you can right click on the focexec and "Edit Text" copy the TABLE FILE....
commands to the END into the post.

Here is a link to the InfoAssist documentation:

Creating HOLD files

You will find the PDF at the end of the list of the links on the left side.

Thank you for participating in the Focal Point Forum!
Tamra Colangelo
Focal Point Moderator
Information Builders

Tamra,

I actually spoke with our IBI representative yesterday over the phone and have been shown a solution, but I do not yet know of how to perform one of the steps within InfoAssist. Basically, the way I was going about it was incorrect. I did have a hold file, but instead of processing all of my information in one hold file, I should have been creating three separate identical hold files for the three fields (primary, secondary, and remaining). After creating these hold files he showed me how to append the three within text editor, but he was not yet sure of the way to append them within InfoAssist itself. He said he would look into it and get back with me.

So, I do understand how to go about this type of report correctly now in terms of hold file creation. That said, if you know how to append three hold files within InfoAssist, then that would be the last piece of the puzzle. If not, then I should consider this post resolved. Below is a short snippet of my code so that you can see the two appends and one of the hold files. I have excluded the other two identical hold files, the table joins at the top, and the creation of the report at the bottom.


FILEDEF SECONDARY DISK primary.ftm (APPEND
FILEDEF REMAINING DISK primary.ftm (APPEND

TABLE FILE SNAPCAT/UTT_CB_FAC_TBL
SUM COMPUTE FLAG/A1='P';
COMPUTE PrimaryArea/D12.2=( UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.PCT_PRI * .01 ) * UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.TOTAL_AREA ;
COMPUTE ActiveFlag/I1=IF UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.FACILITY_ID EQ LAST UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.FACILITY_ID AND UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.EFF_STATUS EQ 'A' THEN 1 ELSE 0 ; NOPRINT
COMPUTE PrimaryCIPDescr/A60=STRIP ( 60 , J001.UTT_FAC_CIP_TBL.DESCR60 , ',' , 'A60' ) ;
BY J004.FACILITY_TBL.BLDG_CD
BY UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.FACILITY_ID AS ('Facility ID', UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.FACILITY_ID, 'Facility_ID' )
BY HIGHEST 1 UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.EFFDT AS ('Effective Date', UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.EFFDT, 'Effective_Date' )
BY UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.CIP_PRI
WHERE UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.EFF_STATUS EQ 'A';
WHERE UTT_CB_FAC_TBL.UTT_CB_FAC_TBL.TOTAL_AREA NE .00;
WHERE TOTAL ActiveFlag NE 1
ON TABLE HOLD AS PRIMARY
END


Thank you.