Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Script/Expression Help Required

Hi

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

TO

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


1 Solution

Accepted Solutions
preminqlik
Valued Contributor II

Re: Script/Expression Help Required

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)

9 Replies
morris70
Valued Contributor

Re: Script/Expression Help Required

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]

Not applicable

Re: Script/Expression Help Required

Hi

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

Thanks,

Glen

Not applicable

Re: Script/Expression Help Required

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

Not applicable

Re: Script/Expression Help Required

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.

thanstad
New Contributor III

Re: Script/Expression Help Required

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

sujeetsingh
Honored Contributor III

Re: Script/Expression Help Required

Glen ,

Just use nested if condition . It will work .

Not applicable

Re: Script/Expression Help Required

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

preminqlik
Valued Contributor II

Re: Script/Expression Help Required

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)

Not applicable

Re: Script/Expression Help Required

That works perfectly, thanks!


Glen

Community Browser