Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have survey responses like expert, beginner etc.
I want to convert all the text to numbers in qlikview
i loaded the file in qlikview but i need for eg expert as 5 beginner as 1
How do i do that.
Is there any function like setting up the value for these text
please help
Use a mapping.Data Cleansing
Create a Mapping Load
MappingTable:
Mapping
LOAD * Inline [
Text, Number
Expert, 5
Intermediate, 3
Beginner, 1
];
FactTable:
LOAD Level,
ApplyMap('MappingTable', Level) as Number
FROM Source;
You could map your values like
MAP:
MAPPING LOAD
old, dual(old, number) AS new
INLINE [
old, number
expert, 5
beginner, 1
];
Then in your survey table, use something like
LOAD
survey,
ApplyMap('MAP', response) AS response
FROM SurveyTable;
edit:
I've used a dual value, so you have both text and number at hand, using num() or text().
maybe an if or a pick/wildmatch or pick/match
load
pick(wildmatch(yourfield, 'expert', 'beginner', '*'), 5, 1, 0) as newfield,
yourfield
inline [
yourfield
expert
expert
beginner
other
];