Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist II
Specialist II

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)

View solution in original post

9 Replies
morris70
Specialist
Specialist

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
Author

Hi

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

Thanks,

Glen

Not applicable
Author

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
Author

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
Creator
Creator

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
Master III
Master III

Glen ,

Just use nested if condition . It will work .

Not applicable
Author

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
Specialist II
Specialist II

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)

View solution in original post

Not applicable
Author

That works perfectly, thanks!


Glen