Hi, I have this table mytable
ID Level Amount1 Amount2 Amount3 Amount4
10 1 9 10 15 6
10 2 2 15 19 2
11 1 5 11 23 0
11 2 6 12 73 0
12 1 7 2 0 0
12 2 3 25 0 0
13 1 5 45 18 0
13 2 7 48 73 0
13 3 2 235 55 0
The prim. keys are ID and Level. For each line I would like to count the nonzeros from the amount1-4 put it in the 3rd column and take out the trailing zeros to produce a .txt file:
mytable.txt:
Non zero Counter
|
V
10, 1, 4, 9 , 10, 15 ,6
10, 2, 4, 2, 15 ,19 ,2
11, 1, 3, 5 , 1 ,23
11, 2, 3, 6 , 12 ,73
12, 1, 2, 7 , 2
12, 2, 2, 3 , 25
13, 1, 3, 5 , 45, 18
13, 2, 3, 7 , 48, 73
13, 3, 3, 2 , 235, 55
My 1st question is how can I use sql query to find the count of nonzero of amount1 - amount4 to produce the counter value for the txt file?
My 2nd question is, I have been using macro with TransferText to transfer table to .txt straight, can I still use it at this case while the number of column may varies depending on the trailing zeros to be omitted?
Thanks
gcyeung
ID Level Amount1 Amount2 Amount3 Amount4
10 1 9 10 15 6
10 2 2 15 19 2
11 1 5 11 23 0
11 2 6 12 73 0
12 1 7 2 0 0
12 2 3 25 0 0
13 1 5 45 18 0
13 2 7 48 73 0
13 3 2 235 55 0
The prim. keys are ID and Level. For each line I would like to count the nonzeros from the amount1-4 put it in the 3rd column and take out the trailing zeros to produce a .txt file:
mytable.txt:
Non zero Counter
|
V
10, 1, 4, 9 , 10, 15 ,6
10, 2, 4, 2, 15 ,19 ,2
11, 1, 3, 5 , 1 ,23
11, 2, 3, 6 , 12 ,73
12, 1, 2, 7 , 2
12, 2, 2, 3 , 25
13, 1, 3, 5 , 45, 18
13, 2, 3, 7 , 48, 73
13, 3, 3, 2 , 235, 55
My 1st question is how can I use sql query to find the count of nonzero of amount1 - amount4 to produce the counter value for the txt file?
My 2nd question is, I have been using macro with TransferText to transfer table to .txt straight, can I still use it at this case while the number of column may varies depending on the trailing zeros to be omitted?
Thanks
gcyeung