topic Re: Edit Script, If Field A = Field B in QlikView

Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 13:39:37 GMT

Hi Guys,

I have two fields coming in from my Load Script

ASSIGNED_TO and a COMPLETE_BY

Is it possible to add another field to the script based on the two fields above that if ASSIGNED_TO = COMPLETE_BY then it creates a Field called MASTER_USER?

Re: Edit Script, If Field A = Field B

Colin-Albert — Mon, 09 Nov 2015 14:14:29 GMT

It is possible, but the method depends on whether the two fields are in the same table.

Can you post an example of your app?

Re: Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 14:20:10 GMT

Hello,

You can create that field like: if(ASSIGNED_TO = COMPLETE_BY, 1, 0) as MASTER_USER.

As Colin says, they both have to be in the same table, or you must join both tables.

Re: Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 14:22:18 GMT

Hi Colin,

I cant post the data as due to confidentiality but yes the fields are in the same table.

Tim

Re: Edit Script, If Field A = Field B

Colin-Albert — Mon, 09 Nov 2015 14:49:44 GMT

You can post examples to the community without compromising any data confidentiality by using reduce to make the application smaller and scrambling the data to randomise the data in the application.

Re: Edit Script, If Field A = Field B

jyothish8807 — Mon, 09 Nov 2015 14:54:10 GMT

Hi Scholar,

Try like this in script:

If both fields are coming from different table then, bring them in on table using resident load or joins and then create this field.

Load

if(ASSIGNED_TO = COMPLETE_BY, 'True','False') as MASTER_USER

from <>

Now in UI in charts use:

if(MASTER_USER='True',MASTER_USER)

Regards

Re: Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 15:28:17 GMT

Hi Jose,

Is there anyway to do this where it returns the actual ID (Either the Assigned_To or Complete_By as they should be the same as the Master_USER?

e.g. if Assigned_To and Complete_BY were both A12345 that it returns A12345 as a selectable value in the MASTER_USER Field?

Thanks,

Re: Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 16:04:20 GMT

Hi,

yes, try with this one:

if(ASSIGNED_TO = COMPLETE_BY, ASSIGNED_TO, null()) as MASTER_USER.

Re: Edit Script, If Field A = Field B

Colin-Albert — Mon, 09 Nov 2015 16:17:41 GMT

>Is there anyway to do this where it returns the actual ID

if(ASSIGNED_TO = COMPLETE_BY, ASSIGNED_TO) as MASTER_USER

Re: Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 16:43:50 GMT

Hi Colin,

Firstly apologies, for what I had originally asked the above is spot on and would be correct. However I have found a flaw my end that ASSIGNED_TO can actually be blank. instead of ASSIGNED_TO=COMPLETE_BY, is there anyway I can merge the two fields to form MASTER_USER.

To explain what I am trying to achieve so you can make more sense of it. I am working on a report in relation to the number of projects being completed.

I want to show Projects that are OPEN(UNASSIGNED), ASSIGNED_TO AND COMPLETE BY.

As Assigned by and Complete by can be different I was hoping to create a joint field so I could use it as a Dimension.

For Instance I want something like:

Master User

ASSIGNED_TO (Count Expression where ASSIGNED_TO = 'Scholar'

COMPLETE (Count Expression where COMPLETE_BY = 'Scholar)

Scholar

107

I hope that makes more sense,

Re: Edit Script, If Field A = Field B

Anonymous — Mon, 09 Nov 2015 16:48:46 GMT

Hi Colin,

in fact, if your ASSIGNED_TO field is the actual ID, it should work. So, are you having any problems after using this expression?

I mean that it should be enough, let me know it if it doesn't work.

Re: Edit Script, If Field A = Field B

Colin-Albert — Mon, 09 Nov 2015 17:52:50 GMT

This logic should work for you.

if(len(trim(ASSIGNED_TO)) = 0, 'OPEN', // tests for blank and nulls

if( ASSIGNED_TO = COMPLETE_BY , COMPLETE_BY, 'NONE')) AS MASTER_USER

Re: Edit Script, If Field A = Field B

Anonymous — Tue, 10 Nov 2015 11:55:08 GMT

Hi Colin,

Thanks for this, it is very close.

If I just wanted to merge the IDs in ASSIGNED_TO and COMPLETE_BY to create the new field MASTER USER. How would that work in the script?

Re: Edit Script, If Field A = Field B

Colin-Albert — Tue, 10 Nov 2015 14:13:35 GMT

You can drop the fields at the end of the script.

drop field ASSIGNED_TO ;