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

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?

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
Not applicable
Author

How about using a MAPping file in the script?

Reagrs,

Gordon

swuehl
MVP
MVP

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
Author

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
Specialist II
Specialist II

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
Author

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
Author

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
Author

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
Author

Thank you, that works!