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

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

But this not:

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

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:

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

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

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;

This will result into:

Possibly related posts:

1. QlikTip #36: Loading a mapping table directly from a QVD file (QV10 SR2+)
2. QlikTip #30: How to delete existing QVD files via load-script
3. QlikTip #3: Check whether a field exists or not (translated and improved)