topic Re: Combining Several Fields in QlikView

Combining Several Fields

Mon, 14 Dec 2015 13:42:14 GMT

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

Re: Combining Several Fields

sunny_talwar — Mon, 14 Dec 2015 15:56:22 GMT

Would you be able to share some raw data with the expected output?

Re: Combining Several Fields

swuehl — Mon, 14 Dec 2015 15:58:06 GMT

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.

Re: Combining Several Fields

Mon, 14 Dec 2015 16:03:07 GMT

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

Re: Combining Several Fields

Mon, 14 Dec 2015 18:44:57 GMT

I am not too familiar with Crosstables. Can you load a crosstable from another table?

Re: Combining Several Fields

JonnyPoole — Mon, 14 Dec 2015 19:02:18 GMT

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;

Re: Combining Several Fields

Mon, 14 Dec 2015 19:15:44 GMT

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

Re: Combining Several Fields

JonnyPoole — Mon, 14 Dec 2015 19:31:23 GMT

Couple more screenshots in case it was not clear...

Re: Combining Several Fields

eduardo_sommer — Mon, 14 Dec 2015 19:39:49 GMT

If you implement Johnathan Poole's script and create a listbox for the MasterCategory field you get what you want

Eduardo

Re: Combining Several Fields

Mon, 14 Dec 2015 19:55:23 GMT

Here is examples of my problem.

Re: Combining Several Fields

JonnyPoole — Mon, 14 Dec 2015 21:47:48 GMT

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

Re: Combining Several Fields

Tue, 15 Dec 2015 14:27:59 GMT

This worked great. Thanks for all the help!