Create a Column with Ifs (better options?)

[Anonymous]

Hi guys,

I'm creating with help of a good colleague a dashboard for a Survey of more than 250 respondents. It's an easy Excel but we are facing an issue. We have applied Crosstables so the table (TABLE_TEMP) right now is

Respondent ID

Year

Team

Manager

Region

Questions

Results

We want to create a new column that would group the questions depending on their nature. We have created a new table that is called TABLE_DETAIL that includes

Respondent ID

Question Group

Question Detail

Response Value

The code we have applied is:

TABLE_DETAIL:

Load

     [Respondent ID]

     If(Questions = 'Question Value 1', 'First Grouping',

     If(Questions = 'Question Value 2', 'First Grouping',

     If(Questions = 'Question Value 3', Second Grouping'))) as "Question Group"

     Questions as "Question Detail"

     Results as "Response Value"

Resident TABLE_DETAIL

There are 17 different types of questions resulting in 5 question groups, which means we would have to add 13 more IFs to our code.

Is there any better way to do this? Either concatenating, or iterating on the first table and with no need of creating a new one?

[vamsee]

Create an Inline table or read a table from excel with two columns (whichever is easier for you to maintain).

LOAD * INLINE [

    Question, Group

    Question Value 1, First Grouping

    Question Value 2, First Grouping

    Question Value 3, Second Grouping

];

Join this to the original table.

[michaelsmith]

To expand upon Vamsee's asnwer, if you were to go with his suggestion and use the link table then the 'question' values within his inline table would need to match those in your original data table exactly.  In essence, the inline table is being used as a reference table which will sit alongside the original data table.

Something else that Vamsee alighted to in his response was the joining of the two tables.  Don't get me wrong it will work if you were to keep the two data table seperate as long as those were joined on a single common field name.  However, what may be neater, would be to join the two table so that all of the data is held within the one table.  This could be done by using the following script:

LEFT JOIN (TABLE_DETAIL)

LOAD * INLINE [

    Questions, Group

    Question1, First Grouping

    Question2, First Grouping

    Question3, Second Grouping

];

Hope this is what you were looking for.

Let me know how you get on.

[vamsee]

You are right Michael about keeping two tables separate would improve the performance and reduce the possibilities of duplicate records.

Also, on the same note if the requirement is to have the new column in the same table I would also suggest a mapping load.

Group_Map:

Mapping Load

                         Questions,

                           Group;

LOAD * INLINE [

    Questions, Group

    Question1, First Grouping

    Question2, First Grouping

    Question3, Second Grouping

];

and then Applymap('Group_Map',Questions, 'Default Group') as [Question Group]