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;
Download Sample Application:
Example QlikView-application containing the code of this article
8 Comments