Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Nestled IF with Match and AND in calculated field

Hi,

 

I'm trying to create this logic:

I have a table with amongst others the fields "Type" (which contains amongst others the entries 'Piece' and 'Hours'), "Payroll" (which contains 'Hourly' and 'Monthly') "Account" (which contains 'F040', 'F0950', 'N0100' amongst others), "Value" (which contains a value), "Postings" (which contains a number value), "Pay" which contains hourly payment.

 

I am trying to build a logic like this:
IF ([Type] = 'Piece' , [Posting]*[Value],
    IF=[Type] = 'Hours',   
         IF([Payroll]='Monthly' AND Match([Account],'F040', 'F0950', 'N0100'),0,[Posting]*[Value]*[Pay])
      ,0)
)

Basically if Type = Piece, just calulate Posting with Value.
IF Type = 'Hours' it needs to first check if Payroll is 'Monthly' and then set it to 0 if the account matches the list given. For the rest of the type 'Hours' it will calculate as per the forumla.

If type is not 'Piece' or 'Hours' it should set value to 0. 

I first tried using OR instead of the MATCH, but figured that might be wrong. 

The error I keep getting is "Missing right paranthesis"... 

Labels (3)
6 Replies
Highlighted
Specialist III
Specialist III

IF ([Type] = 'Piece' , [Posting]*[Value],
    IF([Type] = 'Hours',   
         IF([Payroll]='Monthly' AND Match([Account],'F040', 'F0950', 'N0100'),0,[Posting]*[Value]*[Pay])
      ,0)
)

Highlighted
Contributor III
Contributor III

Hi

 

that is what I have tried for a long time now to do, but I keep getting an error:

Missing right parenthesis.

My exact code is:

IF ([Type] = 'stykk', [Posting (Seconds)]*[Verdi],
	IF ([Type]='timelønn',
    	IF([Lønnstype]='FASTLØNN' AND Match([Account ID],'F040','F0950','N0100'),0,[Posting (Seconds)]*[Verdi]*[Timel])
        ,0)
        )

I have tried several of the steps on their own and they work (espesially the match one). But all together it fails.... 😞 

 

Highlighted
Employee
Employee

That works on my side with Qlik Sense Nov 2018 . What release are you using?
Highlighted
Contributor III
Contributor III

Hi

 

it looks like we're running Qlik Sense June 2018 on our server.

 

I'll take a peek at downloading the newest version privately at home this weekend and see if I can get it to work there. 

Highlighted
Employee
Employee

The expression is valid also in June 2018 on my side.

IF ([Type] = 'stykk', [Posting (Seconds)]*[Verdi],
IF ([Type]='timelønn',
IF([Lønnstype]='FASTLØNN' AND Match([Account ID],'F040','F0950','N0100'),0,[Posting (Seconds)]*[Verdi]*[Timel])
,0)
)
Highlighted
Contributor III
Contributor III

Very strange.

 

If I use the forumla as a master item (measure) in a sheet it works fine, but if I try to create a calculated field it does not work (it seems to hang up on the "AND" statement and gives the paranthesis error)