KX Developer Visual Inspector Multiple X points

[ekallivrousis]

Hello, 

I cant seem to figure out if this is possible but i want to compare 2 different price columns at a time over certain period. Is this possible on the visual inspector? 

My table looks like this:

time         price    pricev2
------------------------------
00:00:00.002 6.33 6.32
00:00:01.001 4.05 4.05
00:00:26.808 4.05 5.07
00:00:27.002 5.12 5.12
00:00:28.002 2.61 2.61
00:00:29.002 4.61 4.47
00:00:30.001 4.9  4.47
00:00:31.000 4.64 4.77
00:00:32.000 2.37 4.87
00:00:33.000 3.75 2.7

I want a line for each price to show how off the prices are at a certain time

[rocuinneagain]

In the visual inspector you can enter qsql queries.

Here are some example which may help. Firstly I recreated your table:

table:flip `time`price`pricev2!(
(00:00:00.002 00:00:01.001 00:00:26.808 00:00:27.002 00:00:28.002 00:00:29.002 00:00:30.001 00:00:31 00:00:32 00:00:33);
(6.33 4.05 4.05 5.12 2.61 4.61 4.9 4.64 2.37 3.75); 
(6.32 4.05 5.07 5.12 2.61 4.47 4.47 4.77 4.87 2.7));

Uses flip to create a table from a dictionary.

You can compare the columns using subtract:

update priceDiff:price-pricev2 from table
time         price pricev2 priceDiff
------------------------------------
00:00:00.002 6.33  6.32    0.01     
00:00:01.001 4.05  4.05    0        
00:00:26.808 4.05  5.07    -1.02    
00:00:27.002 5.12  5.12    0        
00:00:28.002 2.61  2.61    0        
00:00:29.002 4.61  4.47    0.14     
00:00:30.001 4.9   4.47    0.43     
00:00:31.000 4.64  4.77    -0.13    
00:00:32.000 2.37  4.87    -2.5     
00:00:33.000 3.75  2.7     1.05   

You can use within to query within a time window:

select from table where time within 00:00:01 00:00:30
time         price pricev2
--------------------------
00:00:01.001 4.05  4.05   
00:00:26.808 4.05  5.07   
00:00:27.002 5.12  5.12   
00:00:28.002 2.61  2.61   
00:00:29.002 4.61  4.47  

Then you can combine the 2 statements in to one:

update priceDiff:price-pricev2 from select from table where time within 00:00:01 00:00:30
time         price pricev2 priceDiff
------------------------------------
00:00:01.001 4.05  4.05    0        
00:00:26.808 4.05  5.07    -1.02    
00:00:27.002 5.12  5.12    0        
00:00:28.002 2.61  2.61    0        
00:00:29.002 4.61  4.47    0.14    

This page has lots more examples: qsql

Hopefully this helps you.

[ekallivrousis]

Hey rocuinneagain,

Thank you for this but unfortunately this isnt what i was looking for. I want to use KX Developer visual inspector to view a line graph with both price and pricev2 as separate lines against time.

[rocuinneagain · Accepted solution]

One option would be to unpivot the table using a helper function

/tab : the table to operate on
/baseCols : the columns not to unpivot
/pivotCols : the columns which you wish to unpivot
/kCol : the key name for unpivoted data
/vCol :  the value name for unpivoted data
unpivot:{[tab;baseCols;pivotCols;kCol;vCol] 
 base:?[tab;();0b;{x!x}(),baseCols];
 newCols:{[k;v;t;p] flip (k;v)!(count[t]#p;t p)}[kCol;vCol;tab] each pivotCols;
 baseCols xasc raze {[b;n] b,'n}[base] each newCols
 }

unpivot[;`time;`price`pricev2;`priceType;`price] table
time         priceType price
----------------------------
00:00:00.002 price     6.33 
00:00:00.002 pricev2   6.32 
00:00:01.001 price     4.05 
00:00:01.001 pricev2   4.05 
00:00:26.808 price     4.05 
00:00:26.808 pricev2   5.07 
00:00:27.002 price     5.12 
00:00:27.002 pricev2   5.12 
00:00:28.002 price     2.61 
00:00:28.002 pricev2   2.61 
00:00:29.002 price     4.61 
00:00:29.002 pricev2   4.47 
00:00:30.001 price     4.9  
00:00:30.001 pricev2   4.47 
00:00:31.000 price     4.64 
00:00:31.000 pricev2   4.77 
00:00:32.000 price     2.37 
00:00:32.000 pricev2   4.87 
00:00:33.000 price     3.75 
00:00:33.000 pricev2   2.7  

Then you can set the options to graph the lines

[ekallivrousis]

Thanks so much, this is exactly what i was looking for