Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Can CASE statement be used in QV?

I used to use CASE statement in SQL SELECT statements, what would be the equivalent expression in QV - thanks in advance

10 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

I'd recommend ApplyMap().

nagaiank
Specialist III
Specialist III

You may also try

Switch..case..default..end switch

statement (For details, see the Qlikview Reference manual)

Not applicable
Author

Thank you very much for the reply, would you be able to give a example of the Swtich statement in QV - thanks

Not applicable
Author

Thank you very much for the reply, would you be able to give a example of the Switch statement in QV - thanks

nagaiank
Specialist III
Specialist III

An extract from the QV Reference Manual giving example of switch statement is:

Examples:

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

By the way, the manual can be accessed from the help menu. A pdf file of the manual will be in the installation folder of the Qlikview desktop client (C:\Program Files\QlikView\Documentation)

Jason_Michaelides
Luminary Alumni
Luminary Alumni

The advantage of ApplyMap() is it's dynamic and control of the mapping can be passed to appropriate business users with a simple Excel document.  For example, let's say your CASE statement is to translate regions into abbreviations, you can set up an Excel doc:

Region | Abbrev

United Kingdom | UK

United States of America | USA

etc | etc

and appropriate users can control the mapping by being granted access to the Excel file.  Then, in your load script:

Map_Regions:

MAPPING LOAD

     Region

     ,Abbrev

FROM...ExcelDoc...;

Data:

LOAD

     Field1

     ,Field2

     ,Field3

     ,ApplyMap('Map_Regions',Field4,'<Unknown>')     AS     RegionAbbrev

;

SQL SELECT....;

Your Field4 is the full region name in your database.

Now, any changes made by your users would take effect automatically at the next reload without you having to get involved.

Jason

Not applicable
Author

Hi Jason, Many thanks for your reply and for your time.

How can I use values from two fields in the mapping, (that is if the value from a field meets a certain criteria then it should pick the value from another field)

In the SQL statement it would look like this:

CASE when WWHS = "IT' then LLOC ELSE WWHS END

(where WWHS was the warehouse field and if the warehouse was 'IT" then it would be substituted with a value in the Location field)

Not applicable
Author

Many thanks for your reply and for your time.

How can I use values from two fields in the mapping, (that is if the value from a field meets a certain criteria then it should pick the value from another field)

In the SQL statement it would look like this:

CASE when WWHS = "IT' then LLOC ELSE WWHS END

(where WWHS was the warehouse field and if the warehouse was 'IT" then it would be substituted with a value in the Location field)

Jason_Michaelides
Luminary Alumni
Luminary Alumni

ApplyMap() is for looking up values from a field and returning mapped values from a different source.  Are you saying that dependent on the value of WWHS you want to return the current row value from another field?  In this case if WWHS='IT' then use the value from LLOC, otherwise leave it as WWHS.  What if WWHS='IT2'.  Would you return a value from another field entirely?  Maybe LLOC2?  If not then a simple IF() statement might be your best shot:

Data:

LOAD

     Field1

     ,Field2

     ,Field3

     ,IF(WWHS='IT',LLOC,WWHS)     AS     WHSLoc

;

SQL SELECT....;