Case when statement combined with outcome calculation

[#1]

Good afternoon,

I have the following issue/question. I have 2 money-fields which I extracted from each other.

Total -/- partial amounts = outcome:

=(Verplichting_Aanvraag_FreeNumberField_01)-sum(TransactieRegel_Verplichting_BedragIncl) = Outcome

The outcome is (of course) +1 or -1.

I want to add a case when statement:

Case when (Verplichting_Aanvraag_FreeNumberField_01)-sum(TransactieRegel_Verplichting_BedragIncl) =< '0' then 'Afgerond' else (Verplichting_Aanvraag_FreeNumberField_01)-sum(TransactieRegel_Verplichting_BedragIncl) END as 'Status Verplichting'

Somehow I can 't fix this and I hope some one can help me.

--------------------------------------------------------------------------------------------

code from my qvw is (my issue in red):

TRACE ### Transactieregels Verplichting;

SQL SELECT BedragIncl as TransactieRegel_Verplichting_BedragIncl,

  ID  as Verplichting_ID,

    BestelNr as Verplichting_BestelNR

FROM Synergy.dbo.CSCRT_TransactieRegels_View

WHERE BestelNr not like '13032%_'

;

TRACE ### Transacties Verplichting;

SQL SELECT BoekstukNr as Transactie_Verplichting_Boekstuknummer,

  BedragIncl AS Verplichting_Transacties_BedragIncl,

    ID as Verplichting_ID

FROM Synergy.dbo.CSCRT_Transacties_View

Where BoekstukNr not like '13%_' AND BoekstukNr not like '11%_' AND BoekstukNr not like '12%_'

;

TRACE ### Verzoeken Verplichting Type 200;

SQL SELECT Description as Verplichting_BestelNR,

    ID as Verplichting_ID_Verzoek,

    FLOOR (cast(syscreated - getdate() as float)*-1) as Verplichting_Ouderdom,

    syscreated as Bestelbonregel_Verplichting_syscreated

FROM Synergy.dbo.Absences

Where Type = '200'

and Description not like '13032%_'

;

TRACE ### Verzoeken Verplichting Type 204;

SQL SELECT FreeguidField_01 as Verplichting_ID_Verzoek,

RelatedRequestID as Verplichting_Aanvraag_ID

FROM Synergy.dbo.Absences

Where type in (204)

;

TRACE ### Verzoeken Verplichting Type 201 202 203;

SQL SELECT ID as Verplichting_Aanvraag_ID,

FreeNumberField_01 as Verplichting_Aanvraag_FreeNumberField_01

FROM Synergy.dbo.Absences

Where type in (201, 202, 203) AND Status <> '2'

;

[#2 · Accepted solution]

I'm not entirely sure what you're trying to do.

I'm not familiar with this notation:

Total -/- partial amounts = outcome:

What does -/- signify?

And why is the outcome +1 or -1 only?

Can you explain further?

You say you want to add a case statement.  To what?  To the script?  Or do you want to create a case in an expression in the UI?

If you want to add a case statement in the script, then use the standard SQL case syntax in your Select statement something like this:

SQL SELECT

     CASE @TestVal

     WHEN 1 THEN 'First'

     WHEN 2 THEN 'Second'

     WHEN 3 THEN 'Third'

     ELSE 'Other'

END

From TableName

But I didn't see you try to put any such case logic in the script so I'm not sure where you want to do your case.  Can you explain where you want to create your case statement.

If you're trying to add this logic to an expressin in the UI then the correct expression that will work in QlikView would be:

If([Verplichting_Aanvraag_FreeNumberField_01]-sum([TransactieRegel_Verplichting_BedragIncl]) =< '0' , 'Afgerond',  'Status Verplichting')

Finally, your SQL script looks okay to me, what is the problem you're having with it?

[#3]

Dear Terry,

Thank you for your help!

I meant: Total - partial amounts = outcome (just the sum). With the +1 or -1 I meant a positive or a negative amount.

I didn't know what was the best place to put the statement, so I was waiting for som advice, BUT putting it in the expression is the solution for now:

If([Verplichting_Aanvraag_FreeNumberField_01]-sum([TransactieRegel_Verplichting_BedragIncl]) =< '0' , 'Afgerond',  'Status Verplichting')

Thank you very much!

Regards,

Kees-Jan

[djsampat]

Hi Terry or anyone else, can you help me out on my question Month and Year from Date Field