QlikView App Dev

[#1]

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

[Jason_Michaelides]

I'd recommend ApplyMap().

[nagaiank]

You may also try

Switch..case..default..end switch

statement (For details, see the Qlikview Reference manual)

[#4]

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

[#5]

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

[nagaiank]

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]

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

[#8]

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)

[#9]

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]

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