QlikTip #20: Why we do not need a SELECT-CASE/Switch-Case in QlikView load-statements …

I have recently received the question why there is no SELECT-CASE or SWITCH statement available in QlikView within load-statements.

Sure, if you are looking into the reference-manual or into the help file, you’ll find the SWITCH-CASE-statement, but this is a control statement so it cannot be used within a load-statement, e.g. this is possible

[qvl]
switch I
case 1
load ‘$(I): CASE 1’ as case autogenerate 1;
case 2
load ‘$(I): CASE 2’ as case autogenerate 1;
default
load ‘$(I): DEFAULT’ as case autogenerate 1;
end switch
[/qvl]

But this not:

[qvl highlight=”1″]
// Note: this pseudo-code will not work!!!
LOAD
Profession,
(SELECT CASE Profession
CASE ‘Profession A’: 100
CASE ‘Profession B’: 200
CASE ‘Profession C’: 300
CASE ‘Profession D’: 400
DEFAULT: 1000
END SELECT) as RISK_CLASSIFICATION
RESIDENT FirstTable;
[/qvl]

Even if I do not really know why QlikTech has not implemented this, we do not really need it.

Instead of using a SELECT-CASE (SWITCH-CASE) functionality in load-scripts we can easily use ApplyMap method:

Let’s think about the following scenario:

• We have a field called “PROFESSION”
• Depending on the values in this field we want to create a field “RISK_CLASSIFICATION”

Let’s create a sample for loading the field “PROFESSION”, would normally be loaded from your database or other data-sources:

[qvl]
FirstTable:
LOAD * INLINE [
Profession
Profession A
Profession B
Profession C
Profession D
];
[/qvl]

In the next step we create a mapping-table and use the applymap:

[qvl]
Map_Classification:
MAPPING
LOAD * INLINE [
Profession, Classification
Profession A, 100
Profession B, 200
Profession C, 300
Profession D, 400
];

Qualify *;
SecondTable:
Load
Profession,
// Use the applymap to classify the profession, 1000 is the default-value
// if the applymap does not find a match
applymap(‘Map_Classification’,Profession,1000) as RISK_CLASSIFICATION
RESIDENT FirstTable;
[/qvl]

This will result into: