Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

If / switch statement in qlikview expression

Hi all,

I am trying to convert a report to Qlikview, but I'm having a trouble with a case expression.

I want to create a list box with all the sales persons. Unfortunately, each sales person can have many different names (bad data) and appear as "John", "John Doe" or "John D". There are not so many sales persons, so I can do this manually, but I do not get the syntax to work.

I tried the below syntax, but then I end up with a listbox with only "Alex" and "unknown".

'=if(subfield(SALESPERSON,'John'),'John Doe',if(subfield(VAARREF,'Alex'),'Alex Anderson','unknown')

What am I doing wrong? Would I have to do it in the load script instead of the expression-field?

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: If / switch statement in qlikview expression

Mapping is very straightforward - take a look at this:

[Man Map]:
MAPPING LOAD [Manufacturer From],
     [Manufacturer To]
FROM ....

MAP [Manufacturer] USING [Man Map];

[Main Table]:

LOAD [Manufacturer],
...
FROM ....

UNMAP [Manufacturer];


The [Man Map] table source is a 2 column source (e.g. an xls) that holds the values to look for ([Manufacturer From]) and what they should be replaced by ([Manufacturer To]). Its then turned on for a field named [Manufacturer] using the MAP statement, the table [Main Table] has this field name and so it gets applied here and then finally the mapping gets turned off (UNMAP). Thats it!  A table created using MAPPING LOAD is automatically dropped at the end of the script so [Man Map] doesnt even need to be dropped in the code!

Regards,

Gordon

8 Replies
Not applicable

If / switch statement in qlikview expression

How about using a MAPping file in the script?

Reagrs,

Gordon

MVP
MVP

Re: If / switch statement in qlikview expression

Hi mace2011,

what you are doing wrong?

Hm, I think you probably don't want to use subfield (this is for splitting up strings with given delimiter as 2. parameter), you could replace subfield with one of the match functions (e.g. wildmatch() ) instead.

I think a closing bracket is missing (but probably only in your copy in the post).

I would recommend doing the matching in the script, since it is static (you always want to do that, not depending on selection / input).

You could do this in the scipt using your above expression and an AS Fieldname statement.

(SALESPERSON and VAARREF are both fields, right?)

You could probably replace your nested ifs with a pick() function (like a switch statement).

Or, (I think this is what I would do), use a mapping table and applymap to map your different names.

Hope this helps,

Stefan

Not applicable

Re: If / switch statement in qlikview expression

Thanks a lot for your answers!

I'm afraid I'm not very good at SQL and do not know how to create a mapping table, but I will look for information on it.

Is there any good resource to find the formulas and syntax that Qlikview uses? I was not aware of the wildmatch-function, but I am sure you are right.

Also, the formula is like this, I forgot to change some things:

=if(subfield(SALESPERSON,'John'),'John Doe',if(subfield(SALESPERSON,'Alex'),'Alex Anderson','unknown')))

gandalfgray
Valued Contributor

Re: If / switch statement in qlikview expression

Hi Mace

The first place to look for formulas and syntax is "F1" (or menu Help->Contents) when you are in QV.

This gets you into the QlikView Help file where you have a decent help.

Do that and search for "String functions"

qvhelp.JPG

You can also download the QV reference manual from the download area and read!

It goes into more detail.

hth

/gg

Not applicable

Re: If / switch statement in qlikview expression

Mapping is very straightforward - take a look at this:

[Man Map]:
MAPPING LOAD [Manufacturer From],
     [Manufacturer To]
FROM ....

MAP [Manufacturer] USING [Man Map];

[Main Table]:

LOAD [Manufacturer],
...
FROM ....

UNMAP [Manufacturer];


The [Man Map] table source is a 2 column source (e.g. an xls) that holds the values to look for ([Manufacturer From]) and what they should be replaced by ([Manufacturer To]). Its then turned on for a field named [Manufacturer] using the MAP statement, the table [Main Table] has this field name and so it gets applied here and then finally the mapping gets turned off (UNMAP). Thats it!  A table created using MAPPING LOAD is automatically dropped at the end of the script so [Man Map] doesnt even need to be dropped in the code!

Regards,

Gordon

Not applicable

Re: If / switch statement in qlikview expression

So grateful for your help, but there is something I don't get here...

I'm using a select * from [Table] to load data, where the table is part of an ODBC data connection. Where am I supposed to put this in the script when I am also using mapping? If I use LOAD * from [TABLE] it tells me that it can not open the file

(makes sense because it isnt a file...)

Secondly, it tells me that it can not open the xls-file I created with the values. Is there something I forgot?

Sorry for these beginner questions, I find the qlikview way of loading data quite complex and I am very thankful for your help.

Not applicable

Re: If / switch statement in qlikview expression

I dont use SQL connections but I believe that if you use a preceding load statement that should do it:

[Main Table]:

LOAD *;

(SQL statement here)

so it just slots in as my earlier example.

If it doesnt allow you to open the xls containing your from/to mappings try creating the statement in the script with the 'Table Files...' wizard in case you have typos or something in your existing statement.

Regards,

Gordon

Not applicable

If / switch statement in qlikview expression

Thank you, that works!

Community Browser