0

Case Statement in model

Hello!

Wondering if someone could help me with a case statement that i have been unsuccessful with and have tried it many ways...  Any help would be greatly appreciated.

Case(
[SGBSTDN_TERM_CODE_ADMIT] between 201650 and 201940,
"6th Year",
[SGBSTDN_TERM_CODE_ADMIT] between 201950 and 202040,
"5th Year",
[SGBSTDN_TERM_CODE_ADMIT] between 202050 and 202140,
"4th Year",
[SGBSTDN_TERM_CODE_ADMIT] between 202150 and 202240,
"3rd Year",
[SGBSTDN_TERM_CODE_ADMIT] between 202250 and 202340,
"2nd Year",
[SGBSTDN_TERM_CODE_ADMIT] between 202350 and 202440,
"1st Year", "Missing Year"
)

8 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 6 mths ago
    • Reported - view

    Hi  ,

    Your syntax is incorrect for the PQL CASE() statement. See the Help page for detailed syntax, but your example correctly specified in Pyramid would be :

    Case(

       Criteria(

          [SGBSTDN_TERM_CODE_ADMIT] >= 201650 && [SGBSTDN_TERM_CODE_ADMIT] <= 201940,

          [SGBSTDN_TERM_CODE_ADMIT] >= 201950 && [SGBSTDN_TERM_CODE_ADMIT] <= 202040,

          [SGBSTDN_TERM_CODE_ADMIT] >= 202050 && [SGBSTDN_TERM_CODE_ADMIT] <= 202140,

          [SGBSTDN_TERM_CODE_ADMIT] >= 202150 && [SGBSTDN_TERM_CODE_ADMIT] <= 202240,

          [SGBSTDN_TERM_CODE_ADMIT] >= 202250 && [SGBSTDN_TERM_CODE_ADMIT] <= 202340,

          [SGBSTDN_TERM_CODE_ADMIT] >= 202350 && [SGBSTDN_TERM_CODE_ADMIT] <= 202440),

       CriteriaResults(

          “6th Year”,

          “5th Year”,

          “4th Year”,

          “3rd Year”,

          “2nd Year”,

          “1st Year”),

    “Missing Year”

    )

    Hope that helps!

    Ian

      • Tabitha_DeCaprio
      • 6 mths ago
      • Reported - view

       

      Thank you!!!!  Big help.

    • alex_scott
    • 6 mths ago
    • Reported - view

    If you are creating a custom column the case syntax is... 

    Case([SGBSTDN_TERM_CODE_ADMIT] >="201650" and [SGBSTDN_TERM_CODE_ADMIT] <= "201940","6th Year",
    [SGBSTDN_TERM_CODE_ADMIT] >="201950" and [SGBSTDN_TERM_CODE_ADMIT] <= "202040","5th Year",
    [SGBSTDN_TERM_CODE_ADMIT] >="202050" and [SGBSTDN_TERM_CODE_ADMIT] < "202140","4th Year", 
    [SGBSTDN_TERM_CODE_ADMIT] >="202150" and [SGBSTDN_TERM_CODE_ADMIT] < "202240","3rd Year", 
    [SGBSTDN_TERM_CODE_ADMIT] >="202250" and [SGBSTDN_TERM_CODE_ADMIT] < "202340","2nd Year", 
    [SGBSTDN_TERM_CODE_ADMIT] >="202350" and [SGBSTDN_TERM_CODE_ADMIT] < "202440","1st Year", 
    "Missing Year")  

      • Tabitha_DeCaprio
      • 6 mths ago
      • Reported - view

       

      I've tried:

      IF(
      [Term_ID]=[Term_ID],
      SUM(
      [AID_TOTAL]),
      "0"
      )

      and I'm still getting dups.

      • alex_scott
      • 6 mths ago
      • Reported - view

       I am sorry but I don't fully understand what you are trying to achieve.  Would you be able to provide a full explanation along with some sample data (please don't share any real data on this public forum).  

      • Customer Solutions Architect
      • Moshe_Yossef
      • 6 mths ago
      • Reported - view

       

      Can you try a custom column:

      IF(

      [Term_ID]=[Term_ID],

      [AID_TOTAL],

      0

      )

       

      And then define a sum on this custom column?

    • Tabitha_DeCaprio
    • 6 mths ago
    • Reported - view

    Thank you!  Question. Is there a way to create a statement with a sum if or sum attached.  I have a table that has for instance an ID with multiple amounts associate with one ID, I'd like to sum the total for each ID that way I can join the tables.

    Thanks again for all of your help!

      • Customer Solutions Architect
      • Moshe_Yossef
      • 6 mths ago
      • Reported - view

       

      Can describe what your model looks like?

      What are the tables participating in your example and how are they related?

Login to reply

Content aside

  • Status Answered
  • 6 mths agoLast active
  • 8Replies
  • 70Views
  • 4 Following