Looking for each element of "select"ed table in another table

Community Support
1

Good day Gurus, 

Quick question, which I have been struggling with for a few days now:

I have a table (source) t:

ba time                            id              acn 

S  0D10:00:00.268000001 135716992 01  

S  0D10:00:00.291000002 135717309 01 

S  0D10:00:00.314000003 135717803 01 

S  0D10:00:00.357000004 135718358 01  

B  0D10:00:00.445000005 135717329 00 

S  0D10:00:00.507000006 135719868 01  

B  0D10:00:00.593000007 135719974 00  

S  0D10:00:00.802000008 135721378 01 

S  0D10:00:00.819000009 135721498 01  

S  0D10:00:00.840000010 135721620 01  

B  0D10:00:00.849000011 135721669 01  

B  0D10:00:00.854000012 135721703 01  

B  0D10:00:00.855000013 135721704 01 

B  0D10:00:00.859000014 135721739 01  

B  0D10:00:00.860000015 135721743 01  

B  0D10:00:00.860000016 135721746 01  

B  0D10:00:00.869000017 135721797 01  

B  0D10:00:00.874000018 135721545 00  

B  0D10:00:00.874000019 135721821 01  

S  0D10:00:00.874000020 135721822 01  

B  0D10:00:00.875000021 135721543 00  

B  0D10:00:00.875000022 135721825 01  

S  0D10:00:00.875000023 135721826 01  

S  0D10:00:00.990000024 135721967 01  

B  0D10:00:00.993000025 135721972 01  

B  0D10:00:00.995000026 135721972 00  

S  0D10:00:00.995000027 135721983 01  

S  0D10:00:01.000000028 135722004 01  

S  0D10:00:01.002000029 135722011 01 

S  0D10:00:01.002000030 135722004 00 

S  0D10:00:01.003000031 135722022 01  

S  0D10:00:01.003000032 135722023 01  

B  0D10:00:01.003000033 135722024 01 

B  0D10:00:01.003000034 135722026 01 

S  0D10:00:01.004000035 135721983 00  

I need to find those id’s in the table which have acn = 00 and see if there are other records in the table where these same identified id’s have acn=01. If an ID has just one record with acn =00, I need to delete the entire row/record from the table ‘t’.

So I do: 

st: select countID: count id, acn, time by id from t[1+til 40] where acn=00

q)st

id               | countID acn       time

-------------------| ----------------------------------

135717329|  1           ,0x00    0D10:00:00.445000005

135719974|  1           ,0x00    0D10:00:00.593000007

135721543|  1           ,0x00    0D10:00:00.875000021

135721545|  1           ,0x00    0D10:00:00.874000018

135721669|  1           ,0x00    0D10:00:01.004000036

135721972|  1           ,0x00    0D10:00:00.995000026

135721983|  1           ,0x00    0D10:00:01.004000035

135722004|  1           ,0x00    0D10:00:01.002000030

135722011|  1           ,0x00    0D10:00:01.004000038

135722026|  1           ,0x00    0D10:00:01.005000041

I try this: 

st:0!st

update indices: where each st[;id]=\: t[1+til 40; id] from st

id              countID acn   time                             indices

135717329 1          ,0x00 0D10:00:00.445000005 ,3

135719974 1          ,0x00 0D10:00:00.593000007 ,5

135721543 1          ,0x00 0D10:00:00.875000021 ,19

135721545 1          ,0x00 0D10:00:00.874000018 ,16

135721669 1          ,0x00 0D10:00:01.004000036 9 34

135721972 1          ,0x00 0D10:00:00.995000026 23 24

135721983 1          ,0x00 0D10:00:01.004000035 25 33

135722004 1          ,0x00 0D10:00:01.002000030 26 28

135722011 1          ,0x00 0D10:00:01.004000038 27 36

135722026 1          ,0x00 0D10:00:01.005000041 32 39

Now, since I do not know any other way around, I have to make an assumption that those id’s in table ‘st’ that have 1 item in the ‘indices’ column - are the ones which I had identified in the steps above, i.e. those which have acn=00 in table ‘t’.

. 

On the other hand, the id’s in table ‘st’ which have more than one item in the ‘indices’ column are the one which have acn=00 AND acn=01 in table ‘t’. However, this is a big assumption to make, because the same id can pop up several times with ‘acn’=00 which means that the records with several items in table ‘st’ in col ‘indices’ can be the id’s popping up several times and each time with acn=00, not acn=01.

If anyone could suggest how I can solve this problem, that would be very much appreciated. 

Thank you, 

2

q) select from t where id in exec id from (select acn,count i by id from t)where (x=2) or `01=first each acn

3

Not sure I understand the requirements correctly. But if you just want to delete  records when the ID has only 00(including multiple 00) , then 

Select from t where ({`01 in distinct x};acn)fby id

If only delete ID with only one 00, multiple 00 stays, then

Select from t where ({not enlist[`00]~asc x};acn) fby id 

Type in phone,  can’t give examples.

Jay

4

This is exactly what I was after. Could you please point me in the right direction to read up and understand the line, Jay?

Thank you all for your replies.  

5

http://code.kx.com/wiki/Reference/fby

fby is like select …by, but used in where clauses. So (function;acn)fby will group acn based on id, then apply function on the grouped acn.

To see it more clear, you can use select ,by.  Use Kumar’s sample data

t:(ba:100?SB;id:135716000+(100?400);acn:100?0001)

select acn by id from t

id       | acn

---------| ---------

135716190| 0000

135716194| ,`00

135716203| ,`01

135716205| 0001`00

135716213| ,`00

135716269| 0001

135716270| ,`00

135716310| 0001

135716343| 0001

135716360| ,`01

Column acn is grouped by id. In case 1, function is {01 in distinct x}, which finds the records with 01 in the unique grouped acn, i.e., exclude the records that have only `00(s).

Sorry for the late reply. Hope that helps.

Jay