Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking for a way to combine several fields I imported from an excel sheet. Each row has a unique number and 3 options with text. I want to be able to click on one selection field and make it search through all three of them. At the same time I want to maintain the ability to have the separate also.
Thanks
~Norman Rea
i found three things:
The option sometimes was in a delimitted format: Category A or Category C
-> i used subfield to turn it into components and associate both categories to the record
The table had Year and Mission as well so i created a compound key to filter the combination of #,Year, and MIssion by category... not just #. Let me know if you need to change that.
There was a typo in your load script on the first tab. One of hte loads loaded a field named 'misson' instead of 'mission' creating a duplicate table in your data model. unneeded (i think).
Take a look at the attached
Would you be able to share some raw data with the expected output?
You can consider transforming your cross table to a straight table:
CROSSTABLE (Option, Text)
LOAD Number, Option1, Option2, Option3
FROM ExcelFile.xls (...);
Then you can search Text field for entry in any Option fields, or limit Option field to search for specific options.
Here is sample data. While I want to still keep the option fields separate so the I can have the user select something from Option 1, 2 and 3. I also want a master select that I can select the data within a cell such as Category A and get both records 1 an
I am not too familiar with Crosstables. Can you load a crosstable from another table?
This will work. It will create a new field called Category with all the values from option 1 ,2,3. It links those 'master category' values to the actual data records (by #) .
Courses:
LOAD #,
Subject,
Philosophy,
Option1,
Option2,
Option3
FROM
(ooxml, embedded labels, table is Sheet1);
MasterCategories:
Load #,
Option1 as MasterCategory
resident Courses;
Load #,
Option2 as MasterCategory
resident Courses;
Load #,
Option3 as MasterCategory
resident Courses;
I guess what is getting confusing is that my output is a data table and I want to have one field I can select on and give me all the rows that have Category A in either the Option 1, Option 2 or Option 3 spot.
~Norm
Couple more screenshots in case it was not clear...
If you implement Johnathan Poole's script and create a listbox for the MasterCategory field you get what you want
Eduardo
Here is examples of my problem.