If / switch statement in qlikview expression

[#1]

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?

[#2]

How about using a MAPping file in the script?

Reagrs,

Gordon

[swuehl]

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

[#4]

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]

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"

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

It goes into more detail.

hth

/gg

[#6 · Accepted solution]

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

[#7]

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.

[#8]

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

[#9]

Thank you, that works!