Script/Expression Help Required

[#1]

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

[Anonymous]

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]

[#3]

Hi

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

Thanks,

Glen

[#4]

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

[#5]

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]

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]

Glen ,

Just use nested if condition . It will work .

[#8]

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 · Accepted solution]

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)

[#10]

That works perfectly, thanks!

Glen