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

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

Wed, 20 Oct 2010 09:06:01 GMT

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.

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

Wed, 20 Oct 2010 09:17:53 GMT

Hi Daniel, please attach a sample so I can check it out, tx, S

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

Wed, 20 Oct 2010 09:24:29 GMT

Hi,

this could be a part of your load-script:

Load

Cue, // and other fields

If ( Cue='Support', 0, AnsweredCalls) AS [AnsCalls-Support],

// next fields, if any

--> creates a new field called "AnsCalls-Support" during load, content depending on the field "Cue"

Regards

Roland

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

Wed, 20 Oct 2010 09:36:11 GMT

Hi Daniel,

Here's the sample table that solves your problem.

Regards

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

Wed, 20 Oct 2010 09:48:06 GMT

Hi shanejvv, unfortunately I'm still running the Personal Edition version on my computer and from what I understand you can't open .qvw files that are created with a PE-license on another computer.

I will hopefully get a proper license within the coming weeks but I hope I have sorted this out by that time.

Thanks anyway!

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

Wed, 20 Oct 2010 09:49:35 GMT

kurokarl, I will try this out.

Thank you for your quick response!

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

Wed, 20 Oct 2010 10:09:23 GMT

Hi Daniel,

Can you access the file that I had uploaded?

If no just follow this instruction:

In your Expression add this code


If(Cue = 'Support', '0', AnsweredCalls)

Then on Presentation Tab, unchecked Suppress Zero Values.

Regards

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

Wed, 20 Oct 2010 10:15:19 GMT

Unfortunately, we do not have option to insert fields either left or right to use some functions in Qlikview 8 th version.

Is such kind of option is there in latest version??

Thanks,

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

Wed, 20 Oct 2010 10:25:07 GMT

Hi Tim (Daniel),

I do not understand your comment. Do you both work in the same office ? To answer your question: My (load-)solution works fine with 9.x And as far as I remember the old QV8-days, it should work also with that version.

See you

Roland

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

Wed, 20 Oct 2010 10:30:14 GMT

Hi,

I am talking general, let assume you have one table box, if you want to add one more field its not that much feasible as compared to Business objects.

Thanks,

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

Wed, 20 Oct 2010 10:36:32 GMT

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

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

Wed, 20 Oct 2010 10:39:01 GMT

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

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

Wed, 20 Oct 2010 10:45:55 GMT

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

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

Wed, 20 Oct 2010 10:58:43 GMT


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!

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

Wed, 20 Oct 2010 11:06:37 GMT

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?

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

Wed, 20 Oct 2010 13:16:14 GMT

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

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

johnw — Wed, 20 Oct 2010 23:54:30 GMT

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)