Replace values in subfield

[jannarous05]

I hope this is a fairly easy question to solve.  I've just done some searching and can't quite land on the right answer on how to do this.

I have survey data, but the "Multiple choice" answers to one question are all stored in a single column in a csv file, with a space ' ' as delimiter.

I use "subfield" to split these answers out onto their own.

1) I load my main table

2) Then I load my subfield using the following script:

LOAD

     ID,

    Subfield([MCQuestion1],' ') as [MCAnswer]

FROM [lib://Desktop/Multiple Choice Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

It gives me the following result:

ID, MCQuestion1

1, opt1 opt2

2, opt1 opt3

3, opt2 opt4

4, opt2

Then using subfield, I get:

ID, MCAnswer

1, opt1

1, opt2

2, opt1

2, opt3

3, opt2

3, opt4

4, opt2

So I've done that just fine.

But now, I want to change "opt1" to Pizza, "opt2" to Spaghetti, "opt3" to Ice Cream, and "opt4" to Steak (for example).

I've thought that perhaps Replace() or ApplyMap() functions could help me do this.  But I'm not sure how to do this.  Can someone help?

Thanks!

Janna

[Ivan_Bozov]

You can use preceding load like this:

LOAD
     ID,
     IF(MATCH(MCAnswer, 'opt1'), 'Pizza', IF(MATCH(MCAnswer, 'opt2'), 'Spaghetti', IF(MATCH(MCAnswer, 'opt3'), 'Ice Cream', IF(MATCH(MCAnswer, 'opt4'), 'Steak')))) AS MCAnswer;  
LOAD
     ID,
     Subfield([MCQuestion1],' ') as [MCAnswer]
FROM [lib://Desktop/Multiple Choice Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

[sunny_talwar]

Use MapSubstring for this

[jannarous05]

Hi Sunny,

The problem here, is that "Ice cream" has a space in it - so I can't then use "subfield" with a space delimiter.  So I need to apply the mapping after the codes are broken out into the subfield.  Does that make sense?

[jannarous05]

Hi Ivan,

Okay, I'll try this.  My preference would be to use something like "applymap" so I don't have to hard-code the answers right into this script like this.  Do you think that would be possible using a preceding load like in your example?

Thanks,

Janna

[kingsley101 · Accepted solution]

Hi Janna.

Try this:

MAP1:

Mapping

LOAD * INLINE [

MCA, Value

opt1, Pizza

opt2, Ice Cream

opt3, Cake

]

;

ABC:

NoConcatenate

LOAD

     ID,

    APPLYMAP('MAP1', Subfield([MCQuestion1],' '), 'NA') as [MCAnswer]

FROM [lib://Desktop/Multiple Choice Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thanks

[jannarous05]

Worked just like I hoped it would!  This was perfect.

I have looked up the "NoConcatenate" script - I'm not sure exactly what it does even after reading the description.  If I remove it from the script, I get the same result.  Should I have it in there or not?  What's the purpose?

[kingsley101]

Hi Janna.

In case you need clarity on what concatenation is - it is when rows from the second table are appended to the end (bottom) of the first table.

Lets say you want to load a delivery status table:

LOAD * INLINE [

ID, Date, Status

1, 2018/01/01, Incomplete

2, 2018/01/06, Complete

]

;

And you load a table that contains the data from your customers registration:

LOAD * INLINE [

ID, Date, Status

987, 2016/05/24, Active

70, 2012/01/01, Inactive

]

;

Because the field names are the same, it will automatically concatenate, so the NoConcatenate function will make sure the second table does not concatenate onto any table (in this case, the table directly above it). I actually use the NoConcatenate function on every table I load just to be safe. If I need to Concatenate one table to another, I specify the table name like so:

Concatenate(Sales_Fact)

LOAD...

In the above example (The bold text), the result would look like this:

ID              Date                        Status

1                2018/01/01              Incomplete

2                2018/01/06              Complete

987            2016/05/24              Active

70              2012/01/01              Inactive

Hope this helps you understand the function a bit better.

Thanks

Kingsley