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:
Result of the tables in QlikView after using the applymap

Bookmark and Share

and tagged , , , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

8 Comments

  1. paul
    Posted February 7, 2010 at 14:59 | Permalink

    Hi Stenfan , Nice work. May i ask what is the plug in you use for allow you upload sample QV doc in this post ? Hope you can give me the name.
    Paul

  2. Posted February 8, 2010 at 12:42 | Permalink

    Hi Paul,

    it’s no plugin, it is just the standard WordPress-functionality designed for my needs!

    Best regards
    Stefan

  3. paul
    Posted February 11, 2010 at 08:56 | Permalink

    Hi Stefan, Thank you very much for your reply. But when i try to create a new post , i unable to find where to upload QV doc, can you pls give me a few word how you do it ? apprecaite your sharing .
    Paul

    • Posted February 11, 2010 at 09:12 | Permalink

      Sorry, this is absolutely off-topic (not QlikView related). I’ll send you an eMail with some screenshots …

      Best regards
      Stefan

  4. Paul
    Posted February 13, 2010 at 01:45 | Permalink

    Sorry for trouble you. My email is paulyeo@tdstech.com

  5. George Beaton
    Posted July 28, 2010 at 10:42 | Permalink

    I would still maintain that this would be so much easier and readable with a Case statement!

  6. Azadeh
    Posted May 25, 2011 at 10:57 | Permalink

    Hello Stefan
    I am creating a script for loading a qvd file and I want to use Case,When syntax in my script but I am not succeeded.
    I want to know if I can use use Case in this way:

    Load * Inline [
    RIalGroup,Code
    ‘Lower than 1’,1
    ‘between 1 and 2’,2
    ‘between 3 and 4’,3
    ]

    load
    RIalGroup=case when (count=1 and count =2 and count<3) then'3'
    end,
    @1,
    @2,
    @3
    from Azadeh.qvd (qvd);

    Thank you for your attention

2 Trackbacks

  1. […] Possibly related posts:QlikTip #20: Why we do not need a SELECT-CASE/Switch-Case in QlikView load-statements … […]

  2. […] Possibly related posts:QlikTip #20: Why we do not need a SELECT-CASE/Switch-Case in QlikView load-statements … […]