topic Greater than using Date in Discussions

Greater than using Date

penalah — Fri, 09 Jun 2023 17:01:16 GMT

Hello everyone,

I am looking to know on how to write a date function/formula and see only values and data related from 2022

Can anyone please guide me on this.

Thanks

Re: Greater than using Date

txnelson — Mon, 13 Jun 2022 18:08:45 GMT

Use the Year() function

current data table() << select where( Year(:Date) == 2022);

Re: Greater than using Date

penalah — Mon, 13 Jun 2022 19:06:47 GMT

Here is the thing, I have the below where condition and it works it just selects those 4 related data

Where(
In List(
Column( "id", "t1" ),
{"12346", "13456B", "14659R", "19086A"}

)

& In List (

Column( "date", "t2" ),

{"2022"}

)

but when I use the same to write the date it's not populating as I need

I also tried like using >31Dec2021, year{2022}, >2021

May I know what am I doing wrong here?

Thanks

Re: Greater than using Date

pmroz — Mon, 13 Jun 2022 19:42:36 GMT

We need a little more information. Perhaps a sample of your data table, and the code you're using?

Re: Greater than using Date

penalah — Mon, 13 Jun 2022 20:50:18 GMT

Hello, will this do? and in this code what I am looking is how to write date function in where statement for date1 so I can see only 2022 data and not the older ones

please let me know if that makes any sense

New SQL Query(
    Version( 130 ),
    Connection( "ODBC:DSN=\!"\!";Description=\!"\!";UID=\!"\!";PWD=\!"\!";APP=JMP;DATABASE=\!"\!";" ),
    QueryName( "13Jun2022" ),
    Select(
        Column( "class", "t2" ),
        Column( "ID", "t2", Alias( "Attribute" ) ),
        Column( "no", "t5", Alias( "Number" ) ),
        Column( "text", "t2" ),
        Column( "value", "t2" ),
        Column( "units", "t2" ),
        Column( "Date1", "t5", Numeric Format( "ddMonyyyy", "-1", "NO", "" ), Field Width( 23 ) ),
        Column( "condition", "t2", Alias( "Condition" ) ),
        Column( "label", "t2" ),
        Column( "typeid", "t2" ),
        Column( "list", "t2" ),
        Column( "date2", "t2", Numeric Format( "ddMonyyyy", "-1", "NO", "" ), Field Width( 23 ) ),
        Column( "id", "t4" ),
        Column( "date3", "t5", Numeric Format( "ddMonyyyy", "-1", "NO", "" ), Field Width( 23 ) ),
        Column( "date4", "t2", Numeric Format( "ddMonyyyy", "-1", "NO", "" ), Field Width( 23 ) ),
        Column( "testing", "t2", Numeric Format( "ddMonyyyy", "-1", "NO", "" ), Field Width( 23 ) )
    ),
    From(
        Table( "1", Schema( "DB" ), Alias( "t2" ) ),
        Table( "id", Schema( "DB" ), Alias( "t4" ), Join( Type( Left Outer ), EQ( Column( "id", "t2" ), Column( "id", "t4" ) ) ) ),
        Table(
            "batch",
            Schema( "DB" ),
            Alias( "t5" ),
            Join( Type( Left Outer ), EQ( Column( "batch", "t2" ), Column( "batch", "t5" ) ) )
        )
    ),
    Where(
        In List(
            Column( "id", "t4" ),
            {"12340", "12345A", "12345S", "12345A"},
            UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
        ) & In List( Column( "class", "t2" ), {"Q", "M"}, UI( SelectListFilter( ListBox, Base( "Categorical" ) ) ) ) &
        In List( Column( "batch", "t5" ), {"A", "I", "P", "R"}, UI( SelectListFilter( ListBox, Base( "Categorical" ) ) ) )
    ),
    Order By(
        Column( "no", "t5", Order( "Ascending" ) ),
        Column( "id", "t2", Order( "Ascending" ) ),
        Column( "label", "t2", Order( "Ascending" ) )
    )
) << Modify;