topic Re: Script/Expression Help Required in QlikView

Script/Expression Help Required

Mon, 30 Dec 2013 10:37:30 GMT

I am new to Qlikview and would appreciate some asistance on a problem I can't find a solution for.

I have three tables:

Locks:

CONTRACT ACCOUNT

LOCK TYPE

FROM

Workstream:

CONTRACT ACCOUNT,

CONTRACT NUMBER,

DOCUMENT NUMBER,

Balance:

CONTRACT ACCOUNT,

CONTRACT NUMBER,

BALANCE,

What I am trying to do is create a straight table that totals the balance of Contract Accounts that have a Lock Type of '1' that are not in the Workstream table.

I'm using the following expression:

sum

({<[LOCK TYPE = {'1'}>}if(isnull(CONTRACT NUMBER), BALANCE, 0))

But because there are multiple contract accounts with 'u' type locks, it multiples the total balance by the number of times it features in the table.

Any help would be much appreciated,

Thanks,

Glen

Re: Script/Expression Help Required

Anonymous — Mon, 30 Dec 2013 11:09:20 GMT

Not sure if this is the problem but it looks like you are missing a ] in the field LOCK TYPE. I guess it should be [LOCK TYPE]

Re: Script/Expression Help Required

Mon, 30 Dec 2013 11:13:15 GMT

No, I just pasted it over here incorrectly - it does have the extra ] in the actual expression.

Thanks,

Glen

Re: Script/Expression Help Required

Mon, 30 Dec 2013 11:15:40 GMT

Hi,

Personally when there is a space in the field name I always put [] around the words. Along with the missing "]" as Nils points out there might be need for [ ] around Contract Number.

Tyrone

Re: Script/Expression Help Required

Mon, 30 Dec 2013 11:16:44 GMT

As a thought, if the document can be posted it might be wise to do that so people can have a quick play with the actual data.

Re: Script/Expression Help Required

thanstad — Mon, 30 Dec 2013 11:37:02 GMT

Hello Glen,

Iam not completely sure how yopur data in this three tables look like but I have tried to make an expample. I have simply made an flag field in the script on LOCK TYPE and using this in the Straight table. Normally flags are a lot more efficient than make all possible calculations in the SET ANALYSIS.

Hopefully is this something you could find useful !

Happy new QlikYear !

/Tormod Hanstad

Re: Script/Expression Help Required

sujeetsingh — Mon, 30 Dec 2013 11:49:55 GMT

Glen ,

Just use nested if condition . It will work .

Re: Script/Expression Help Required

Mon, 30 Dec 2013 11:55:04 GMT

Thanks Tormond

So, I only want to see contract accounts with a '1' type lock from the locks table, but they have not been assigned to a workstream - so must not be in that table at all. The problem I am having is that a contract account can have multiple '1' type locks with different from and to dates, so when I am summing to balance - it's multiplying it.

Does that make sense?

Thanks,

Glen

Re: Script/Expression Help Required

preminqlik — Mon, 30 Dec 2013 11:55:57 GMT

Hey sibley hi, try this

Locks_Map:

mapping load

CONTRACT ACCOUNT

LOCK TYPE

FROM

XXX

Workstream:

CONTRACT ACCOUNT,

applymap('Locks_Map',CONTRACT ACCOUNT,null()) as LockType,

CONTRACT NUMBER,

DOCUMENT NUMBER,

Balance:

CONTRACT ACCOUNT,

CONTRACT NUMBER,

BALANCE,

drop table Locks_Map;

hope this helps u....!

---> sum({< LockType={1}>}BALANCE)

Re: Script/Expression Help Required

Mon, 30 Dec 2013 15:50:45 GMT

That works perfectly, thanks!

Glen