cancel
Showing results for 
Search instead for 
Did you mean: 

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

lengieshar
New Contributor
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];
 {[d;g;t;i;x]@[d;x;g;t[x]i]}[d:par[d;p;n];g;r;<r f]'!r;
 @[;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
6 REPLIES 6

keevey
New Contributor II
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

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

lengieshar
New Contributor
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];
 {[d;g;t;i;x]@[d;x;g;t[x]i]}[d:par[d;p;n];g;r;<r f]'!r;
 @[;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

try P (timestamp type)

Cheers,
   Attila

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

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

q)r:flip`date`open`high`low`close`volume`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