Creating values in a field based on values in two other fields

[#1]

Hi.

I have a table that looks like the one below:

Cue IncommingCalls AnsweredCalls
Sales 5 4
Support 3 2

What I would like to do is adding a field that displays a 0 if the value of the Cue field is support.
It would then look like this:

Cue IncommingCalls AnsweredCalls AnsCalls-Support
Sales 5 4 4
Support 3 2 0

There is probably an easy solution to this but I'm a beginner when it comes to QlikView.

I'm guessing that it might be possible to solve this with load inline.

[#2]

This confuses me.

In Karl's solution you can add his newly created field in a table box just a normal field (by clicking add button)

In my solution I used straight table so you will add the code in expression.

I think both solutions will work in v8

[#3]

Hi Tim,

Oh, I understand what you mean .

For me, I am more a script-guy. If I need a new (calculated) field (i.e. like you in a tablebox), I prefer to generate it during loading. This costs no enduser-time and I have it present when I need it in the next object. And in most cases, you will need it.

regards

Roland

[#4]

Hi Ronald,

this is correct. There are often more than one solution and both should work with V8. As I just said, I prefer creating such fields in the script.

I took a glance into your qvw-file. If you don't mind, I can repost it with my "normal" client, so evrerybody should be able to open it.

Regards

Roalnd

[#5]

kurokarl wrote:
I took a glance into your qvw-file. If you don't mind, I can repost it with my "normal" client, so evrerybody should be able to open it.

Sure!

[#6]

kurokarl, that would be very nice of you if you could do that.

Your solution works great (thanks!!) but it is always nice to see in what ways you can get around a problem.

I have a follow up question... 🙂

I have about 10 other fields that I need to do the same thing for and when we will start using this thing 'live' there will be around one million lines in this table.

Is it efficient to do one IF-statement for each value in the Cue-field or is it possible/more efficient to do it in the same IF-statement?

[#7]

Hi Daniel,

• here is Ronald's qvw-file showing a solution in the expressions.
• yes, you can use nested ifs (look at my example below) in scripts
• No, these if-functions are normally not relevant for performance issues. One million rows are not too much. Think about the whole system: performance of the source database (or file), network topology (and traffic), ... In this chain QV normally is not the bottle-neck.
• you can only use one statement (but nested funcions) per field and load, regardless what type of functions
• take a look at the applymap()-function, using a reference-table (in QV it is called a mapping-table) this may make your life easier

LOAD

......

IF (LEN(VProdukt) > 0, VProdukt, VArtikelgruppe) AS Produkt, // VProdukt is empty or Null
VYear,
IF(MONTH(VDate) <4, 'Q1',
IF(MONTH(VDate) <7, 'Q2',
IF(MONTH(VDate) <10,'Q3',
IF(MONTH(VDate) <13,'Q4',
'Q5')))) AS Quartal,

......

VYear, VDate, VProdukt, .... come from an oracle database.

regards

[johnw]

So, you want a table that does the same thing, but has a column for every single Cue? I wouldn't want to create a separate field for every Cue value.

There's probably a better solution, but here's one. Create a new table like this:

LOAD 'AnsCalls-' & fieldvalue('Cue',iterno()) as ExcludeCue
AUTOGENERATE 1
WHILE len(fieldvalue('Cue',iterno()))
;

Turn your chart into a pivot table. Add ExcludeCue as a dimension. Move it to the top. Add this expression:

if(Cue=mid(ExcludeCue,10),0,AnsweredCalls)