Problems of reading "Z" Datetime: when using .Q.dcfgnt to do the

Community Support
1

Hello guys, when I use .Q.dcfgnt (http://code.kx.com/wiki/Cookbook/LoadingFromLargeFiles) to do the partitioned data for my csv file, I got a problem: this script can read the “D” type value, but cannot read the “Z” type value. I was thinking to change something in the script, but after many times of tests, it doesn’t work. Therefore, is anybody who can help me fix this? Appreciate very much!

I put the online script here:  -  I need to put “ZFFFFIS” rather than “DFFFFIS” in the last steps; then it cannot work. I’ve added one thing such as the following: , but it said “d”$c wrong type…
/k)dcfgnt:{[d;c;f;g;n;t]*p if[10>0;c:“d”$c;dpfgnt[d;;f;g;n]‘?[t;;0b;()]’,:'(=;c;)'p:?[;();();c]?[t;();1b;(,c)!,c]]}

.Q.dcfgnt sripts are:

\d .Q
/ extension of .Q.dpft to separate table name & data
/  and allow append or overwrite
/  pass table data in t, table name in n, : or , in g
k)dpfgnt:{[d;p;f;g;n;t]if[~&/qm’r:+en[d]t;'unmappable]; &nbsp;{[d;g;t;i;x]@[d;x;g;t[x]i]}[d:par[d;p;n];g;r;\<r f]'!r; &nbsp;@[;f;p#]@[d;`.d;:;f,r@&~f=r:!r];n}

/ generalization of .Q.dpfnt to auto-partition and save a multi-partition table
/  pass table data in t, table name in n, name of column to partition on in c
k)dcfgnt:{[d;c;f;g;n;t]*p dpfgnt[d;;f;g;n]‘?[t;;0b;()]’,:'(=;c;)'p:?[;();();c]?[t;();1b;(,c)!,c]}

\d .

r:flipdateopenhighlowclosevolumesym!("DFFFFIS";",")0: w:.Q.dcfgnt[:dbo;date;sym;,;stats] .Q.fs[w r@]:file.csv

2

Looks like this is because you are trying to partition on the ‘date’ column which in this case is type z (float based) which is invalid. You can only partition on certain data types - date, int, month. You’ll need to add a suitably typed column on which to partition before trying to use .Q.dcfgnt Sean

3

Thanks Sean. Here the .Q.dcfgnt cannot read Z type of datetime.  I would like to fix it such that I put if[10>0;c:“d”$c; try to change the format in the code; but it is not working;

I’m not familiar with the q programming - thus even though I can partition on other columns such as int, month, it is also necessary to change the code, which I don’t know how to do it. Can you please show me how to fix this:

k)dcfgnt:{[d;c;f;g;n;t]*p dpfgnt[d;;f;g;n]‘?[t;;0b;()]’,:'(=;c;)'p:?[;();();c]?[t;();1b;(,c)!,c]}

Thank you!

? 2015?4?29??? UTC-4??4:24:29?Sean Keevey???

Looks like this is because you are trying to partition on the ‘date’ column which in this case is type z (float based) which is invalid.

You can only partition on certain data types - date, int, month.

You’ll need to add a suitably typed column on which to partition before trying to use .Q.dcfgnt

Sean

4

Is there anyone helping me, since this is very emergent!

Thanks a lot.

? 2015?4?28??? UTC-4??5:12:50?lengie shar???

Hello guys, when I use .Q.dcfgnt (http://code.kx.com/wiki/Cookbook/LoadingFromLargeFiles) to do the partitioned data for my csv file, I got a problem: this script can read the “D” type value, but cannot read the “Z” type value. I was thinking to change something in the script, but after many times of tests, it doesn’t work. Therefore, is anybody who can help me fix this? Appreciate very much!

I put the online script here: - I need to put “ZFFFFIS” rather than “DFFFFIS” in the last steps; then it cannot work. I’ve added one thing such as the following: , but it said “d”$c wrong type…
/k)dcfgnt:{[d;c;f;g;n;t]*p if[10>0;c:“d”$c;dpfgnt[d;;f;g;n]‘?[t;;0b;()]’,:‘(=;c;)‘p:?[;();();c]?[t;();1b;(,c)!,c]]}

.Q.dcfgnt sripts are:
----------------------------------------------------------------------------------------------------------------

\d .Q
/ extension of .Q.dpft to separate table name & data
/ and allow append or overwrite
/ pass table data in t, table name in n, : or , in g
k)dpfgnt:{[d;p;f;g;n;t]if[~&/qm’r:+en[d]t;‘unmappable];<br> {[d;g;t;i;x]@[d;x;g;t[x]i]}[<wbr>d:par[d;p;n];g;r;&lt;r f]'!r;<br> @[;f;p#]@[d;`.d;:;f,r@&~f=r:!r];n}

/ generalization of .Q.dpfnt to auto-partition and save a multi-partition table
/ pass table data in t, table name in n, name of column to partition on in c
k)dcfgnt:{[d;c;f;g;n;t]*p dpfgnt[d;;f;g;n]’?[t;;0b;()]’,:’(=;c;)'p:?[;();();c]?[t;();1b;(,c)!,c]}

\d .

r:flip`date`open`high`low`close`volume`sym!(“DFFFFIS”;“,”)0:
w:.Q.dcfgnt[`:dbo;`date;`sym;,;`stats]
.Q.fs[w r@]`:file.csv

5

try P (timestamp type)

Cheers,

   Attila

6

Thanks Attila. “P” may be able to read the unix format time. However, here the “d” date has to be passed through the functions’ calculations, such that the very long datetime format cannot be used.

I was thinking to insert “d”$ to change the date format in order to call the functions; but my way is wrong (I’m not familiar with the q coding)

? 2015?4?30??? UTC-4??3:34:33?Attila???

try P (timestamp type)

Cheers,

   Attila

7

Just read the date column as a timestamp and then cast to date before you use .Q.dcfgnt

q)r:flipdateopenhighlowclosevolume`sym!(“PFFFFIS”;“,”)0:

q)w:.Q.dcfgnt[:dbo;date;sym;,;stats]

q).Q.fs[w ![;();0b;enlist[`date]!enlist`date.date]r@]`:file.csv

539j

q)\l dbo

q)stats

date sym open high low close volume

------------------------------------------------
2006.10.03 AMD 24.5 24.51 23.79 24.13 19087300
2006.10.03 MSFT 27.37 27.48 27.21 27.37 39386200
2006.10.04 AMD 24.1 25.1 23.95 25.03 17869600
2006.10.04 MSFT 27.39 27.96 27.37 27.94 82191200
2006.10.05 AMD 24.8 25.24 24.6 25.11 17304500
2006.10.05 MSFT 27.92 28.11 27.78 27.92 81967200
2006.10.06 AMD 24.66 24.8 23.96 24.01 17299800
2006.10.06 MSFT 27.76 28 27.65 27.87 36452200

Thanks

Tom