KX Community

Find answers, ask questions, and connect with our KX Community around the world.
KX Community Guidelines

Home Forums kdb+ Selecting date from table

  • Selecting date from table

    Posted by voiddump on January 17, 2024 at 12:00 am

    Hi,

    I’m having trouble selecting the column TRADE_DT. I don’t even know what’s wrong here…

     

    voiddump replied 8 months ago 6 Members · 6 Replies
  • 6 Replies
  • megan_mcp

    Administrator
    January 17, 2024 at 12:00 am

    Hi ,

    To rename table columns you can use xcol cols, xcol, xcols | Reference | kdb+ and q documentation – Kdb+ and q documentation (kx.com)

    Let me know if this works.

    Thanks,

    Megan

  • gyorokpeter-kx

    Member
    January 17, 2024 at 12:00 am

    How did you create the table? Did you load it from a CSV or similar file? You might want to open it in a hex editor because you might have invisible special characters in there. The clue is that the vertical bar on the line of TRADE_DT is further to the left than for the other columns – q takes the length of the column name in bytes to find where to draw that line, and if there are invisible characters, those will shorten the actually printed width of the name, resulting in the line appearing to the left. The same occurs if you have a UTF-8 input file where some characters are represented by a different number of bytes than the number of character slots they take up on the screen. However such characters are not recommended for use in column names because then you can’t use a select query.

    As a quick fix you could use .Q.id on the table which will transform all column names into valid identifiers by removing all invalid characters. However if the source file is used by other applications, they could also run into a similar issue so it might be worth investigating if there is anything wrong with it.

  • unterrainer_ale

    Member
    January 17, 2024 at 12:00 am

    I would avoid using ChatGPT for KDB/Q. It’s often very far off. Read Q for Mortals, it’s a good start to learn Q

  • amcnaught921

    Member
    January 17, 2024 at 12:00 am

    Hello,

     

    It is somewhat hard to fully answer without seeing the data, but I mocked up the following
    test:([] TRADE_DT:enlist .z.d; S_INFO_WINDCODE:`test; S_DQ_CLOSE:1.1; S_DQ_AMOUNT:2.2; S_DQ_VOLUME: 3.3)

    meta test
    c | t f a
    —————| —–
    TRADE_DT | d
    S_INFO_WINDCODE| s
    S_DQ_CLOSE | f
    S_DQ_AMOUNT | f
    S_DQ_VOLUME | f

    And from this I could do your simple select statement, there are scenario’s where column name can cause issues with select statements (e.g. if they started with underscores), in this case you can use functional select (however not required here I will post for this simple example)
    ?[`test;();0b;(enlist `TRADE_DT)!enlist `TRADE_DT]

    Both returned:

    TRADE_DT
    ———-
    2024.01.17

  • voiddump

    Member
    January 17, 2024 at 12:00 am

    Thanks for helping! I tried but it still returns error message. I’ve attached a snapshot of what my table looks like.

    Also I’m considering changing the name of the columns, which may sidestep this problem, I guess? But I don’t know how to change the name. ChatGPT told me to use

    test: ( `date `WindCode `close `amount `Volume) set test

    However, this doesn’t change the headings, they’re still

    `TRADE_DT `S_INFO_WINDCODE `S_DQ_CLOSE `S_DQ_AMOUNT `S_DQ_VOLUME

  • sujoy

    Member
    January 24, 2024 at 12:00 am

    The error represents “TRADE_DT” is not in the table. Most likely you are overriding “test” somewhere in the code, which does not have the column “TRADE_DT”.

    As such the query has no issues, it should work.

Log in to reply.