I have a list of data from 2015-2016 and want to keep the two years together based on a common data point.  I would also like to exclude anything that does not have both years on file because I only care about the year to year change, so single values are just clutter.  So my table looks something like this.  I want to take out the "oranges" line.  Thanks!   edit: and by "apples" "pears" etc., I mean, unique reference numbers--these being the data point that I would like to connect the years to.  2015    apples  4   $0.79 2016    apples  2   $0.83 2016    oranges 14  $1.12 2015    pears   22  $0.74 2016    pears   17  $0.79
122182
How do I 'Remove Non-Duplicates' keeping only the duplicate records? I know I can highlight duplicate records with a different color, but I'd prefer to remove the non-duplicates instead, as I don't want to scroll though thousands of records looking for the needle in the haystack.  I want to keep the duplicates and remove the slingletons.
77300
I want to subtract a constant number from my data to get new data file. For example, I want to subtract  10 from my data.   data.txt  Id  a   b   c   d   e 1   1   2   3   4   5 2   2   3   4   5   6 3   3   4   5   6   7 4   4   5   6   7   8 5   5   6   7   8   9 6   6   7   8   9   10 7   7   8   9   10  11 8   8   9   10  11  12 9   9   10  11  12  13 10  10  11  12  13  14 11  11  12  13  14  15 12  12  13  14  15  16 13  13  14  15  16  17 14  14  15  16  17  18   Required output:  out.txt   Id  a   b   c   d   e 1   -9  -8  -7  -6  -5 2   -8  -7  -6  -5  -4 3   -7  -6  -5  -4  -3 4   -6  -5  -4  -3  -2 5   -5  -4  -3  -2  -1 6   -4  -3  -2  -1  0 7   -3  -2  -1  0   1 8   -2  -1  0   1   2 9   -1  0   1   2   3 10  0   1   2   3   4 11  1   2   3   4   5 12  2   3   4   5   6 13  3   4   5   6   7 14  4   5   6   7   8
3862
I want to do calculations with dates and use 'current year' in a formula condition. When I tried to manipulate dates in a formula I always receive an error of incompatibles types; NUMBER AND DATE.   Because of that I had to convert the date fields into numbers in the dataflow. It worked but I didn't resolve the issue of current year because it is impossible to convert a field that change with time. The condition and the formula is the following:  case     when  'Date_Month'+'Date_Year'*12&gt; 2+'current year'*12 then 0 when ('current-'Date_Year')*12+(2-'Date_Month')) % max('Billing_Periodicity') == 0 then 'Parcel_Bill' else 0    Many Thanks for your attention and help
85188
I have my references as a text file with a long list of entries and each has two (or more) fields.   The first column is the reference's url; the second column is the title which may vary a bit depending on how the entry was made. Same for third field which may or may not be present.  I want to identify but not remove entries that have the first field (reference url) identical. I know about sort -k1,1 -u but that will automatically (non-interactively) remove all but the first hit. Is there a way to just let me know so I can choose which to retain?  In the extract below of three lines that have the same first field (http://unix.stackexchange.com/questions/49569/), I would like to keep line 2 because it has additional tags (sort, CLI) and delete lines #1 and #3:  http://unix.stackexchange.com/questions/49569/  unique-lines-based-on-the-first-field http://unix.stackexchange.com/questions/49569/  Unique lines based on the first field   sort, CLI http://unix.stackexchange.com/questions/49569/  Unique lines based on the first field   Is there a program to help identify such "duplicates"? Then, I can manually clean up by personally deleting lines #1 and #3?
19174
I need to remove every line that has a value of 2 or less in the 8th field (column).   My data looks like this:  12-31   Airport 189 379 41  49.70946503 -124.91377258   2   2880    30.8 01-01   AlberniElementary   165 331 16  49.26100922 -124.80662537   4   5760    26.1 01-09   BamfieldMarine  161 323 23  48.83490372 -125.13572693   2   2875    27.4 01-10   BamfieldMarine  161 323 23  48.83490372 -125.13572693   3   3068    38.6   I understand that using awk I can strip off the values desired and print them to another file, and I understand that sed would edit the current file. In either case, I need to retain the original file.   Note: Please provide thorough explanations with your solutions. It is not suffice to just write the command, please annotate suggestions.   Further note: The data has a header line, so most likely solution will need to      awk 'FNR >1'   I suppose?
108733
I have a table that looks like this:  RECIPE     VERSION_ID     INGREDIENT    PERCENTAGE 4000       100            Ing_1          23,0 4000       100            Ing_100         0,1 4000       200            Ing_1          20,0 4000       200            Ing_100         0,7 4000       300            Ing_1          22,3 4000       300            Ing_100         0,9 4001       900            Ing_1           8,3 4001       900            Ing_100        72,4 4001       901            Ing_1           9,3 4001       901            Ing_100        70,5 5012       871            Ing_1          45,1 5012       871            Ing_100         0,9 5012       877            Ing_1          47,2 5012       877            Ing_100         0,8 5012       879            Ing_1          46,6 5012       879            Ing_100         0,9 5012       880            Ing_1          43,6 5012       880            Ing_100         1,2   There are 100 ingredients per recipe/version. I'd like to display the data from this table like this:  RECIPE     INGREDIENT_Vxxx     PERCENTAGE_Vxxx     INGREDIENT_Vyyy     INGREDIENT_Vyyy (ETC) 4000       Ing_1               23,0                Ing_1               20,0 4000       Ing_100             0,1                 Ing_100              0,7   Because in different versions of recipes, ingredients could be removed or added, I'd like to display both ingredient and percentage per version per recipe. There's also the difficulty that different recipes have different number of versions.  I'm not even sure if this is possible at all or where to begin. Maybe with the PIVOT function?  Could anyone please point me in the right direction?
34214
How can I efficiently create a unique index on two fields in a table like this: create table t (a integer, b integer);  where any unique combination of two different numbers cannot appear more than once on the same row in the table.  In order words if a row exists such that a=1 and b=2, another row cannot exist where a=2 and b=1 or a=1 and b=2.  In other words two numbers cannot appear together more than once in any order.  I have no idea what such a constraint is called, hence the 'two-sided unique index' name in the title.  Update: If I have a composite key on columns (a,b), and a row (1,2) exists in the database, it is possible to insert another row (2,1) without an error.  What I'm looking for is a way to prevent the same pair of numbers from being used more than once in any order...
17820
I am wondering if it is possible to use LINQ to pivot data from the following layout:  CustID | OrderDate | Qty 1      | 1/1/2008  | 100 2      | 1/2/2008  | 200 1      | 2/2/2008  | 350 2      | 2/28/2008 | 221 1      | 3/12/2008 | 250 2      | 3/15/2008 | 2150   into something like this:  CustID  | Jan- 2008 | Feb- 2008 | Mar - 2008 | 1       | 100       | 350       |  250 2       | 200       | 221       | 2150
98776
Is there a way to display the unique values in a column ONLY?  Example:   AAPL AAPL AAPL AAPL AKAM AMZN AMZN AMZN AMZN AMZN AMZN  Expected result:   AAPL AKAM AMZN  I have already done a sort for multiple factors, and the first match for each is the best match.  I'm not sure if this is a vlookup or something else.
77769
For many of my datasets I have the same imported data duplicated numerous times, each iteration displaying the data values for a different column (in my case it's fish catch weight per gridded cell per year, so lots of iterations of the same grid, with a different pattern of values per year).  For presentation purposes, it strikes me as helpful to use the same scale for each year, so I go to the original data and divide the maximum value into (arbitrarily) 10 classes and manually input the values and labels after setting the desired colour ramp and input data field:   This is extremely tedious, not just the manual excel calculation part, but also the input interface for the values &amp; labels*, hence my intention to make use of the 'save style' option. HOWEVER: when I save the style as QML and load it for the next layer, it saves everything - including the data field (in my case, year). When I change this field, QGis automatically generates the values &amp; labels based on the data for that field, ditching all of the values &amp; labels I set.   Is there any way to avoid this happening &amp; thereby duplicate class number, values and labels for each field by simply loading a style? (somewhat meta) Am I going around this the wrong way, OR, is there a different face I could use to climb this mountain?
79673
e.g,  foo1 foo2 foo10 foo100   rather than  foo1 foo10 foo100 foo2   Update: not interested in coding the sort myself (although that's interesting in its own right), but having the database to do the sort for me.
91941
$ cat data.txt  aaaaaa aaaaaa cccccc aaaaaa aaaaaa bbbbbb $ cat data.txt | uniq aaaaaa cccccc aaaaaa bbbbbb $ cat data.txt | sort | uniq aaaaaa bbbbbb cccccc $   The result that I need is to display all the lines from the original file removing all the duplicates (not just the consecutive ones), while maintaining the original order of statements in the file.  Here, in this example, the result that I actually was looking for was  aaaaaa cccccc bbbbbb   How can I perform this generalized uniq operation in general?
67901
I have the following data:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  1   |     ...       |     ...      | MAIN ST           |  2   |     ...       |     ...      |         MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | BERRY LN          |  5   |     ...       |     ...      | BERRY LN          |  6   |     ...       |     ...      | JOHN RD           |  7   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   where the id's are any value, as long as the road column is unique.   Is there a simple way to do this in SQL or T/SQL? I followed the guide , but it only applies to duplicate keys with non-unique rows.
56163
I have dates in 2 tables. Table student is as below:   ID|Name|Examdate 1  John 01/10/2015 2  Fred 11/12/2014   Table retake as below:   ID|RetakeDate 1  02/10/2016 2  05/11/2015   The IDs in retake relate to the students' IDs in student, i.e. they are the same.  I am trying to create another table where only students who have taken a retake after 12 months or more exist. The difference between examdate and RetakeDate needs to be >= 12months.  How do I achieve this?  There could be multiple rows in both tables for the same student. There will be multiple rows on both tables, as this will allow me to see how many times they have taken a retake and how many times an exam. At this moment in time I don't want to see if the retake is for the latest exam. I just want to see how many retakes they have taken all together. End result should be comparing each Exam date with Retake date. Some more explanation:     Q: So  if a student has dates in 2012, 2013, 2014 and then 2 retakes in 2016?       A: There should 3 rows in the result (one for each date)      Q: What if it's three exams – in 2012, 2013, 2014 – and (a) two retakes – in 2014 and 2016? or (b) one retake – in 2014? What should be expected as the result in each of those cases?          A: It doesn't matter. As long as the (a) retake was taken after 12 months or more, they should appear in the result.
77188
I have a spreadsheet of my bank account transaction history in this format:    | A          | B        | C        | D 1 | Date       | Amount   | Balance  | Category 2 | 2013-03-10 | $ -10.59 | $3700.76 | Food shopping 3 | 2013-03-11 | $ - 5.10 | $3695.66 | Car parking 4 | 2013-03-11 | $ 100.00 | $4695.66 | Salary   In my spreadsheet the Balance column is hidden because I recalculate it myself.  I want to have a summary for each month with data looking like this:    | A       | B        | C           | D 1 | Month   | Income   | Expenditure | Net 2 | 2013-03 | $ 100.00 |     $ 15.69 | $ 84.31 3 | 2013-04 | $  00.00 |     $ 00.00 | $ 00.00   These are the formulas I have right now, so I can compute Net fine, but I don't know how to compute Income or Expenditure on a monthly basis because SUMIF only lets me do criteria on a single column (in this case, the Date column, but I want to only sum rows &gt;0.0 for Income and &lt;0.0 for Expenditure):  Net = =SUMIF( MonthAndYear( Txn!$A:$A ) , $A$2, Txn!$B:$B )
101232
I have a text file with 9 fields separated by :  survey:m1111771::rent:travel:::Morning:   How do I pull field 1 and 8 only to a separate file  for example it would look like this,  survey:Morning
32424
I have a table with the following structure:  CREATE TABLE `Rings` (     ID_RingType CHAR(2),     Number MEDIUMINT UNSIGNED,     ID_User INT(11) );   and with data:  INSERT INTO `Rings` VALUES   ('AA',1,1),   ('AA',2,1),   ('AA',3,1),   ('AA',11,1),   ('AA',12,1),   ('AA',13,1),   ('AA',14,1),   ('AA',15,1),   ('AB',16,1),   ('AB',17,1),   ('AB',18,1),   ('AB',19,1),   ('AB',20,2),   ('AB',21,2),   ('AB',22,2);   I wish to group the data based on ID_User and ID_RingType and for each contiguous range of numbers list the MIN and MAX.  The results should look like:  ID_User | ID_RingType | MIN  | MAX 1       | 'AA'        | 1    | 3 1       | 'AA'        | 11   | 15 1       | 'AB'        | 16   | 19 2       | 'AB'        | 20   | 22   I went through several posts on this topic but was not able to tweak them to fit my data.  Any help would be appreciated.