Create a new field

[rubenacampos]

Hello,

I have the following sample data:

Primary Program	Can the tool be used for MIL	Can the tool be used for CW	Can the tool be used for IIS	Can the tool be used for ENV
CW	No	Yes	NA	No
CW	No	Yes	NA	No
CW	No	Yes	NA	No
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
ENV	NA	NA	NA	NA
MIL	NA	NA	NA	NA
MIL	NA	NA	NA	NA
MIL	Yes	No	Yes	No
MIL	Yes	No	Yes	No
MIL	Yes	No	Yes	No

I would like to create a new field with a load script called "Secondary Program(s)" when the tool can be used for a program(s) in addition to the Primary.  I do not want to list the primary program in the secondary.  In the above NA and No are the same.  

[lblumenfeld]

Do you mean "If any of the other fields are "Yes" then Secondary Program should be "Yes"?

Or, do you mean that the Secondary Program should contain MIL, CW, IIS, ENV, accordingly?

Could you provide some additional detail on what you're trying to do?

[rubenacampos]

Hello and thank you for asking.

The data currently does not have a "Secondary Program" field but I would like to create this new field to capture additional program(s) where the tool can be used. 

If there is a "Yes" in the can the tool be used column(s), then in the new Secondary Program field should contain MIL, CW, IIS, ENV, accordingly.

Thank you.

[lblumenfeld]

One more quick question and then I'll post an answer.

Can the tool be used for more than one other program? In that case would you want the secondary program to contain all of them on one single record as in

Secondary Program = "CW, IIS"

or something else?

[rubenacampos]

When the tool can be used for a secondary program(s) it would be nice to list the secondary programs for each record on one line in a description pop-up box but it would also be extremely beneficial to be able to filter on "CW', "IIS", "ENV" ect. and display all tools regardless of primary or secondary.  Is this possible? 

[lblumenfeld · Accepted solution]

Here is a solution that I think will do everything you want. It will create two tables:

Programs

This contains a list of unique Primary Programs and a field containing a comma-separated list of their possible Secondary Programs.

Secondary Link

This contains a list of unique Primary Program / Secondary Program combinations

----

You can use the [Secondary Programs] field as a measure to display a nicely formatted field of Secondary Programs for Primary Program.

You can use the data in the Programs Table as dimensions in Filters and Tables in order to filter Programs and/or Secondary Programs.

Remember to edit the data source in the first Load to point to your data. I added some additional "yes" entries to test with multiple Secondary Programs and it worked.

If you ever add more columns for additional Secondary Programs then just add them to each of the first two load statements in the example.

... I hope this helps.

// Bring the data in so that we only read the spreadsheet once
[Temp Programs]:
LOAD Distinct
	[Primary Program],
	[Can the tool be used for MIL],
	[Can the tool be used for CW],
	[Can the tool be used for IIS],
	[Can the tool be used for ENV]
 FROM [lib://Downloads/Tools.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Transpose the data to a vertical list of Programs and Secondary Programs
[Temp Secondary Link]:
CrossTable ([Secondary Program], Value)
Load
	[Primary Program],
	[Can the tool be used for MIL] as MIL,
	[Can the tool be used for CW] as CW,
	[Can the tool be used for IIS] as IIS,
	[Can the tool be used for ENV] as ENV
Resident [Temp Programs];

// Filter to unique Primary/Secondary Program combinations where the
//     secondary is used ("yes") and it's not the same as the Primary.
[Secondary Link]:
NoConcatenate Load Distinct
	[Primary Program],
	[Secondary Program]
Resident [Temp Secondary Link]
Where Lower(Value) = 'yes' and [Primary Program] <>	[Secondary Program];

Drop Table [Temp Secondary Link];

// At this point, we have created a table or Primary and Secondary Programs
//     that can be used in filters, etc.

// Next, create a table of unique Primary Programs
Programs:
NoConcatenate Load Distinct
	[Primary Program]
Resident [Temp Programs];

// Add a field containing a comma-separated list of Secondary Programs.
Left Join (Programs)
Load
	[Primary Program],
    Concat([Secondary Program], ', ') as [Secondary Programs]
Resident [Secondary Link]
Group by [Primary Program];

Drop Table [Temp Programs];
    
// At this point, we have made a table containing unique Primary Programs
//     with a field containing a comma-separated list of
//     their poosible Secondary Programs. That field can be displayed as a
//     measure in a table.

[rubenacampos]

Amazing!!! Thank you.