Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
;
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?
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?
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
Hi Terry or anyone else, can you help me out on my question Month and Year from Date Field