Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jannarous05
Contributor II
Contributor II

Replace values in subfield

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

1 Solution

Accepted Solutions
kingsley101
Contributor III
Contributor III

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

View solution in original post

7 Replies
Ivan_Bozov
Luminary
Luminary

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);

vizmind.eu
sunny_talwar

Use MapSubstring for this

jannarous05
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

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