3 Replies Latest reply: Nov 3, 2014 3:21 AM by Dhruv Sampat RSS

    Case when statement combined with outcome calculation

    KJ Hof van 't

      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'

      ;

        • Re: Case when statement combined with outcome calculation
          Terry Kavouras

          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?

          • Re: Case when statement combined with outcome calculation
            KJ Hof van 't

            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