topic Re: Can CASE statement be used in QV? in QlikView

Can CASE statement be used in QV?

Sat, 21 Jul 2012 19:40:31 GMT

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

Re: Can CASE statement be used in QV?

Jason_Michaelides — Sat, 21 Jul 2012 19:43:26 GMT

I'd recommend ApplyMap().

Re: Can CASE statement be used in QV?

nagaiank — Sat, 21 Jul 2012 19:44:47 GMT

You may also try

Switch..case..default..end switch

statement (For details, see the Qlikview Reference manual)

Re: Can CASE statement be used in QV?

Sat, 21 Jul 2012 19:54:35 GMT

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

Re: Can CASE statement be used in QV?

Sat, 21 Jul 2012 19:54:49 GMT

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

Re: Can CASE statement be used in QV?

nagaiank — Sat, 21 Jul 2012 20:09:36 GMT

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)

Re: Can CASE statement be used in QV?

Jason_Michaelides — Sat, 21 Jul 2012 21:59:45 GMT

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

Re: Can CASE statement be used in QV?

Sat, 21 Jul 2012 22:09:27 GMT

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)

Re: Can CASE statement be used in QV?

Sat, 21 Jul 2012 22:10:19 GMT

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)

Re: Can CASE statement be used in QV?

Jason_Michaelides — Sun, 22 Jul 2012 09:47:40 GMT

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

Re: Can CASE statement be used in QV?

Anonymous — Mon, 23 Jul 2012 14:09:20 GMT

Hi!

With mappings, you have the option to define the default value when you apply it:

ApplyMap ('MapName', MapField, 'defaultIfNull');

In the default value, you can add another mapping expression. So if you don't have a match in the first map, you can use the second map. Or you could concatenate 2 mapping tables into one.

If you have more complex stuff with a lot of rules, then the best approach woult be to join the main table the other needed tables with the values you want, to a temp table:

TempTable:

Select field1, field2, field3 from yourmaintable.

Left Join

Load field3, field4 resident maptable1; /*These are not qlikview maps, but normal tables that you will delete in the end of the code.*/

Left Join

Load field3, field5 resident maptable2;

Then with a load resident of the temp table, insert your logic to select the

FinalTable:

Load *, if(isnull(Field4), Field5,Field4) as yourfieldName;

Load * resident TempTable;

Drop Fields Field5, Field4 from FinalTable;

Drop tables TempTable, maptable1, maptable2;

Its very important that you use a left Join, in order to mantain the whole data from the temp table.

Hope it helps.