• RELEVANCY SCORE 4.09

    DB:4.09:Compare Two Columns And Find Unique Values kc




    I have two columns and need to keep the unique values from BOTH columns.For example:Column 1abcColumn 2bcdDesired results:ad('a' and 'd' are unique to both column 1 and column 2)How on earth? Thanks,Kurt

    DB:4.09:Compare Two Columns And Find Unique Values kc

    Thanks Barry!This is a great solution. And it works with two columns that have a different number of rows, even when a row has the same value.This is a correct solution to my problem.

  • RELEVANCY SCORE 3.47

    DB:3.47:Combination Of List Columns As Unique Values- List Settings 18




    Hi,
    I'll find that there is option in list column settings as Enforce unique values, but its applicable for single column only. I need to make combinations of list columns as unique.
    Howmake two or more columns as unique in sharepoint 2010 list?
    Anandhan.S

    DB:3.47:Combination Of List Columns As Unique Values- List Settings 18

    also please take a look at my reply at
    http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/394a345f-a44b-421d-8e7f-1366d396216c

  • RELEVANCY SCORE 3.18

    DB:3.18:Find Unique Values In A Row From 2 Different Columns 11




    I have some data that looks similar to this:
    Column1 Column2 Column3 Column4 Column5
    GoodData 123 GoodData ABC GoodData
    GoodData 123 GoodData BCA Gooddata
    GoodData 456 GoodData BCA Gooddata
    and so on....
    each Columns 2 and 4 have duplicate values. I need to each unique instance where both those columns are you unique though. So if Column2 says 123 and Column4 says ABC for 1 row, but another row Column2 says 123 and Column4
    says BCA I want both rows. Additionally I want both rows if Column4 is the same both rows but Column2 is different. Hopefully that makes sense. I have tried doing with sort -unique with group-object and multiple sort -unique pipes with no luck.
    I cant seem to figure out the trick to getting what I am looking for. Any ideas would be appreciated.

    Thanks

    Chris

    Chris Morgan

    DB:3.18:Find Unique Values In A Row From 2 Different Columns 11

    Sorry for the confusion. David has what I was looking for. Only I used sort -unique instead of group to give me the proper output I was looking for. This gave me unique instances of the combined columns 2 and 4.

    Thanks everyone. Chris Morgan

  • RELEVANCY SCORE 3.10

    DB:3.10:Re: How To Create A "Conditional" Unique Key? jk


    Rene
    it is not a trick it is a unique index which simply requires the unique combination of the values of its two columns.

    DB:3.10:Re: How To Create A "Conditional" Unique Key? jk

    well, the OP says he do not care about old dates, so there is no need to index dates. Your solution is easy, it is probably more performant than mine, but it does not allow to have duplicates values for old dates, so it is not the same result.

    the original message states:
    For old data a date column has a value for current data this columns is empty. yes, it does not states that for old data, a date column has an unique values, so your solution is maybe to restrictive

  • RELEVANCY SCORE 3.07

    DB:3.07:Compare Multiple Columns In Microsoft Access 2010 78


    Hi All,

    I was wondering if anyone could help me with this issue...I have 2 spreadsheets in Excel, csv format.
    And I am trying to compare the 2 files which should contain the same data in 3 columns.
    So the first file I have is csv in the format below
    Code,SearchCategoryCode,SearchCategoryValue
    and the second file I have has the exact same csv format
    Code,SearchCategoryCode,SearchCategoryValue.
    I need to find all records where the 3 values are different..
    for example I could have the following scenario in file 1
    Tile,Color,Red
    Tile,Color,Green
    If in File 2 only contains
    Tile,Color,Red
    Then I need to extract out Tile,Color,Green

    If that makes sense.

    Thanks a million for any information at all
    Cheers

    Aidan

    DB:3.07:Compare Multiple Columns In Microsoft Access 2010 78

    1. Link the 2 CSV files so that they appear as Linked Tables in your Access. For the purpose ofthis post and the SQL later, let'sname the 2 Linked Tables as [File1] and [File2].
    1. Create aSELECT Query with the following SQL:
    SELECT F1.*
    FROM File1 AS F1
    LEFT JOIN File2 AS F2
    ON ( (F1.Code = F2.Code)
    And (F1.SearchCategoryCode = F2.SearchCategoryCode)
    And (F1.SearchCategoryValue = F2.SearchCategoryValue) )
    WHERE F2.Code Is Null

    Run the above Query and it should return all rows in File1 that don't have matching rows in File2.
    Van Dinh

  • RELEVANCY SCORE 3.03

    DB:3.03:Compare Values Of Two Columns as


    I have dataset with 2 columns like this
     
    col1   col2
    1       2
    3       5
    7       4
     
    I need to compare values of Col1 and col2 for each row. If threre is a row, where value of col1 grate then value of col2, return true, else false.
    How can I realize this in local reports.

  • RELEVANCY SCORE 3.00

    DB:3.00:Proc Compare pk



    Can I use proc compare to compare and output equal values in more than two columns in the same dataset? Say for the following, each class has different combinations of different columns for weight, but I want them all extracted from the bigger dataset:

    Class Wt1 Wt2 Wt3 Wt4 Wt5

    1 67 89 56 56 45

    2 72 72 45 87 23

    3 43 78 99 43 88

    Thanks!

    DB:3.00:Proc Compare pk


    I'm not sure what output you want.

    Assuming I know what you want it can be done with COMPARE but is probably easier using an (array do etc.).

    Here is my take on proc compare. You probably would want some code to gen the VAR/WITH statements which gets us back to (array do etc.).

    Re: proc compare

    data Wt;

    input Class Wt1-Wt5;

    cards;

    1 67 89 56 56 45

    2 72 72 45 87 23

    3 43 78 99 43 88

    ;;;;

    run;

    proc compare data=wt noprint outstat=stat(where=(_type_ eq 'NDIF' and BASE eq 0));

    by class;

    var wt1 wt1 wt1 wt1 wt2 wt2 wt2 wt3 wt3 wt4;

    with wt2-wt5 wt3-wt5 wt4-wt5 wt5;

    run;

    proc print;

    run;

    Re: proc compareObs Class VAR WITH TYPE BASE COMP DIF PCTDIF

    1 1 Wt3 Wt4 NDIF 0 0 . .

    2 2 Wt1 Wt2 NDIF 0 0 . .

    3 3 Wt1 Wt4 NDIF 0 0 https://communities.sas.com/i . .

    https://communities.sas.com/pre

  • RELEVANCY SCORE 3.00

    DB:3.00:Filter Records With Multiple Columns Using Unique Value For One Column fs


    I have a spreadsheet with 6 columns. There is one column that contains duplicates in it (call it A:A). I want to filter the entire sheet using the unique values in that one column. The end result are the records with all six columns where the values
    in A:A are all unique. I cannot seem to get this to work.

  • RELEVANCY SCORE 3.00

    DB:3.00:Re: Primary Key And Unique Key jz


    Hi ,

    if user entered duplicate values in the column of any table after that if developers/ DBA wanna ALTER table by usonh primary key constraint or unique key constraint

    It will not be allowed Some ORA erroe will appear

    2) You cannot enter more that one null value in columns having UNIQUE KEY constraint

  • RELEVANCY SCORE 2.98

    DB:2.98:Proc Compare Datasets Which Increase In Size mk



    Hi All

    I need to check that all the records in a earlier extracted dataset appear identically in the later extracted larger dataset.

    The first dataset has a unique recordid for each obs. I need to check that these recordids are in the second larger extract (of the same data) and then compare each variable value to see if any of the data values have changed.

    The second dataset is a later snapshot of the first and recordids could be missing, new ones added or data values changed. I just want to find out if my original data is missing or changed in anyway in the second dataset.

    Each obs has a unique recordid

    Any suggestions on how I can achieve this please?

    Many thanks

    Steve

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: How To Obtain (Audit) This ? p3


    Asked to Learn wrote:
    I have more then 50 columns. Your example says i have to write more then 50 insert statement. Is there any other easy way to find out the only change column and it's value ?Well, for insert and delete all columns do change. For update you can compare :OLD and :NEW values and based on that insert into audit table.

    SY.

    DB:2.95:Re: How To Obtain (Audit) This ? p3

    sybrand_b wrote:
    Why would anyone do his work for free?
    He was 'Asked to Learn', wasn't he?Dear Sybrand Bakker,

    Did you be Senior DBA from the very first day of your life ? You didn't need any experience to be DBA ?

    I'm a newbie and created this thread at _3 aug 2012_ and still strugling to solve this problem. So, i'm not giving time on the problem ?
    This forum is full of volunteers and none are paid.

    If you don't want to be a volunteers, may be this forum isn't for you.

    Hope you understand my problem....

    Asked to Learn

    Edited by: Asked to Learn on Sep 3, 2012 6:19 PM

  • RELEVANCY SCORE 2.94

    DB:2.94:Problem In Creating Unique Key On Columns. s8


    HI!

    I have a table like below:
    A B C OccuredDate D ---- Columns
    Null Null 12 2010-04-08 14:47:57.000 Reference ------ Rows
    Null Null 12 2010-04-08 15: 52:58.000 Reference

    E.g,
    A,B,C, OccuredDate, D are columns in the table. I want to create unique key on the columns. SO, If I create the unique key on column OccuredDate, then row would be unique or not (ALL values on both the rows are same except the value of time in OccuredDate
    Column)??

    Thanks,

    DB:2.94:Problem In Creating Unique Key On Columns. s8


    I will have to update those rows in the table later.

    You don't have to put DUMMY data into those columns. You can just keep them NULL.
    You can also define more than one UNIQUE key or UNIQUE index on a table.
    If you don't need the unique constraint for FOREIGN KEY reference or design tool requirement, you can just use UNIQUE index.

    You can only have 1 PRIMARY KEY on a table but several UNIQUE KEYS (or unique indexes).

    Kalman Toth, SQL Server Business Intelligence Training;
    SQLUSA.com

  • RELEVANCY SCORE 2.94

    DB:2.94:Unique Key xz


    hi
    i am having a doubt.
    we r using the combination of unique keys for table loads and also we r using outer joins on that unique keys.
    and also in that unique key combination, some columns are null.
    will unique key will work for outer join , based on null values??

    thx

    DB:2.94:Unique Key xz

    Maybe you should try it then?

  • RELEVANCY SCORE 2.94

    DB:2.94:Compare Column aa


    I have a excel worksheet with 9 columns, I would like to compare each column with each other to find the repetead entries.

    DB:2.94:Compare Column aa

    Given your source data as posted in A2:C4 ...
    if you want to compare items with all others within that range
    and to CFtocolor only those uniqueitems,
    you could apply CF on A2:C4 (with A2 active)
    using this as the CF formula:=COUNTIF($A$2:$C$4,A2)=1

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Compare Srtings To Find Word Matches ja


    Hi Pravin,

    Thanks a lot for the reply...

    Here I need to compare two COLUMNS (Main Strings) and display only the columns having 2 or more words (Sub strings) matchings.

    BR,
    gvk

    DB:2.93:Re: Compare Srtings To Find Word Matches ja

    Hi gurus,
    we have a requirement where in I need to compare two VARCHAR columns of different tables and display only the records having 2 or more words matching in the main strings.
    And the other criteria is that i need to Ignore special Characters while comparing.

    For Eg:
    Compare DESCRIPTION columns in MTL_SYSTEM_ITEMS_B and MTL_ITEM_FLEXFIELDS and display only the records having 2 or more words matching in DESCRIPTION column.

    I would appreciate if anybody could help me with this.

    Thanks,
    gvk.

    Edited by: gvk on Nov 23, 2010 4:10 AM

  • RELEVANCY SCORE 2.93

    DB:2.93:Populating 200 Column Values From Another Row To A New Row Via Trigger x8


    I have a table of 200 columns. I have created a template row with the values in all the columns. The application inserts a new row with values only in 4 columns (that are unique). Rest of the columns are blank. What is the best way to populate rest of the column values from the template row upon insert?

    DB:2.93:Populating 200 Column Values From Another Row To A New Row Via Trigger x8

    So, you can alter table modify (column default value), then you might get.

    SQL create table tab1 (col1 number primary key, col2 varchar2(20));

    Table created.

    SQL insert into tab1 values(1,'Data1');

    1 row created.

    SQL insert into tab1 values(2,'Data2');

    1 row created.

    SQL insert into tab1 values(3,null);

    1 row created.

    SQL alter table tab1 modify(col2 default 'None');

    Table altered.

    SQL insert into tab1(col1) values (4);

    1 row created.

    SQL select * from tab1;

    COL1 COL2
    ---------- --------------------
    1 Data1
    2 Data2
    3
    4 None

    SQLOr, Do you want to set default value on COL1=3.

  • RELEVANCY SCORE 2.92

    DB:2.92:Primary Key And Unique Key k9


    We can have only one primary key and many unique keys on a table.

    Primary key is same as a unique key with no null values.

    So can't we have multiple primary keys by making multiple unique keys with not null constraint on the columns involved in the unique keys.

    DB:2.92:Primary Key And Unique Key k9

    Anish's post is wonderful. It demonstrates implementation of multiple unique identifiers perfectly.

    However, we have a problem with semantics.

    In relational design, any column or combination of columns that uniquely identifies a row is referred to as a Unique Identifier, or Candidate Key (however, I never see the term Candidate Key in Oracle documentation).

    The "Primary Key" is the Candidate Key chosen by the database designer as the one that is most easily recognizable to the developers and users. This is why Oracle only allows one Primary Key constraint per table.

    When the designer cannot identify a column or set of columns which easily distinguisehes each row from the rest of the rows, then they specify that the Primary Key will be system generated(ie. Employee Number, Order Number, etc.). In Oracle the Sequence object is frequently used for this. A system generated key is called a Surrogate Key. (Surrogate Key is another term I don't usually find in Oracle documentation either).

  • RELEVANCY SCORE 2.90

    DB:2.90:Copying A Row From Worksheets Based On Value In Third Worksheet ms


    I have two worksheets "FileMakerPro" and "Skyward" with data in column A of both worksheets. The third worksheet "Unique" contains only the unique values found in column A of both worksheets. What I am trying to do in VBA is compare the value found in the
    first row of the "Unique" worksheet, find the matching value in either of the two other worksheets and then copy that matching rows contents and then replace the "Unique" worksheets rows values from the other worksheet. For example:

    If A1 of the Unique worksheet contains the number 250, and that number exists in the FileMakerPro worksheet, , replace the Unique!A1 with the row contents of FileMakerPro. Here is the code I have so far:

    Sub getAndCopyRows()
    Dim bottomD As Integer
    Dim c As Range
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws2 = ThisWorkbook.Worksheets("Unique")

    For Each ws1 In Sheets(Array("FileMakerPro", "Skyward"))
    ws1.Activate
    bottomD = Range("A" Rows.Count).End(xlUp).Row
    For Each c In Range("A1:A" bottomD)
    For Each ws2 In Sheets
    If ws2.Name = c Then
    ws2.Activate
    c.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Next ws2
    Next c
    Next ws1
    End Sub

    Thank you for your help.

  • RELEVANCY SCORE 2.89

    DB:2.89:Difference In Percentage jc



    Hi,

    I need to compare 2 columns(A with B) values and find out the difference in % in next column I used Dif function but giving only count difference is any other function to identify difference in percentage.

    ABC(Difference in %)502040100307015013510

    I used below code

    data one;

    input A B;

    cards;

    50 20

    100 30

    150 135

    ;

    run;

    data Diff;

    Set one;

    c = (A- B);

    run;

    DB:2.89:Difference In Percentage jc


    Hi,

    If you are trying to display values in % then try this.

    data one;input A B;cards;50 20100 30150 135;

    run;

    data Diff;Set one;c = (A-B)/A;format c percent6.1;run;

  • RELEVANCY SCORE 2.88

    DB:2.88:Proc Report Across Columns 1p



    I have code which produces a report where the number of columns may vary based upon the dataset using the ACROSS specification. Column grand totals are stored in unique macro variables GT_1 .... GT_x for as many columns as exist in the data. My question is, with a varying number of columns, is there any way to place the GT_x values above those columns (e.g., (N = 234), etc.)?

    DB:2.88:Proc Report Across Columns 1p


    Hi:

    It is possible to do you you want because you can nest ACROSS variables under other ACROSS variables in order to get another level of variable -- which gives you another level of header. The fact that you are creating macro variables suggests that you have some degree of macro knowledge. Possibly something like a user-defined format might work, using CNTLIN, as already suggested.

    Without more information from you, like seeing your existing code or getting an idea of your data in more detail, it's hard to provide specific advice.

    Here's a very simple example, that uses a DATA step program to make the "extra" header variable from a macro variable and then uses the NOCOMPLETECOLS option of PROC REPORT to get only 1 header above every unique value of AGE. See attached screenshot.

    cynthia

  • RELEVANCY SCORE 2.86

    DB:2.86:Compare Column In Two Datasets And Output Count 7z



    I am trying to compare two datasets data1 and data2.

    I have the column name in both "var"

    and would like to check if there any differences in those two columns in the two datasets

    and then count the number of differences

    I then would like to call this macro function and save the count to macro variable x

    Somehow this doesnt work any help is appreciated. Thanks!ee

    %macro CompareUniqueCols(data1, data2, var);

    %* check if all the unique values in a column var of data set 1 and data set 2 are same and outputs the number of differences;

    proc sql;

    create table temp1 as select distinct var from data1 order by var;

    run;

    proc sql;

    create table temp2 as select distinct var from data2 order by var;

    run;

    proc compare base = temp2 compare = temp1 outall outnoequal out = outcompare ;

    run;

    proc sql noprint;

    select count(*) into: noCompareObs from outcompare ;

    run;

    noCompareObs;

    %mend;

    %let x = %CompareUniqueCols(data1, data2, var);

    DB:2.86:Compare Column In Two Datasets And Output Count 7z


    You are confused about what a macro is. SAS macro language is a tool to generate code. In general you cannot treat a macro as if it was a subroutine function.

    Here is an example of how you might modify your macro to allow the user to specify the macro variable that you want it use to return the counts.

    %macro CompareUniqueCols(data1, data2, var, mvar=x);
    %local noCompareObs;
    ...
    %let mvar=noCompareObs;
    %mend;

    %let x=;
    %CompareUniqueCols(data1=today,date2=yesterday,var=CustomerID, mvar=X);

    %put Result = x ;

  • RELEVANCY SCORE 2.86

    DB:2.86:Defining Primary Keys kp


    How do you define Primary Key with multiple columns (say two) where the values of the first column such as extract date is not unique and the values of the second column such as sequence number is unique. The combined value of the two columns will be unique. For example

    Extract Date Seq Number
    20070701 001
    20070702 002
    20070703 003

    If you can please send my the syntax of the table definition.
    Thanks.

  • RELEVANCY SCORE 2.86

    DB:2.86:Get Id From Sheet1 And Match With Sheet2id If It Does Not Match Paste Values 18


    Hello
    I have lots of rows and columns in sheet1 with Unique ID. I want to get ID from Sheet1 and compare with Sheet2's ID if it doesn't match paste that ID with values in sheet2.
    I want to get it in VBA code.

    Sheet1 Sheet2

    ID B C D ID B C D
    950 abc 12bc vc5q 950 abc 12bc vc5q

    205 def 32 mn bd52w

    Please help.

    DB:2.86:Get Id From Sheet1 And Match With Sheet2id If It Does Not Match Paste Values 18

    Sub Test()
    Dim R As Range, C As Range

    With Worksheets("Sheet1")
    'Visit each used cell in column A
    For Each R In .Range(.Range("A1"), .Range("A" Rows.Count).End(xlUp))
    'Search in column A in Sheet2
    Set C = Worksheets("Sheet2").Columns("A").Find(R, LookIn:=xlValues, LookAt:=xlWhole)
    'Found?
    If C Is Nothing Then
    'Get next row below the data
    Set C = Worksheets("Sheet2").Range("A" Rows.Count).End(xlUp).Offset(1)
    'Copy this row to sheet2
    R.EntireRow.Copy C
    End If
    Next
    End With
    End Sub

  • RELEVANCY SCORE 2.85

    DB:2.85:Execution Plan Using Index Scan Instead Of Index Seek With Perf Drawback 8d


    Hello,
    I'm trying to find out why i'm the DB engine is not choosing the best plan in the following basic case :
    I have a table TExtract with about 1millions rows.
    3 columns part of a clustered unique index :

    EntityDate DESC (low cardinality about 20 distinct values) EntityName (low cardinality 13 distinct values) ID_pk (extremely high cardinality)
    We only need to get the last data for each entity, therefore I usually join my queries with the result of :

    SELECT MAX(EntityDate) AS LastEntityDate, EntityName
    FROM TExtract
    GROUP BY EntityName

    DB:2.85:Execution Plan Using Index Scan Instead Of Index Seek With Perf Drawback 8d

    Adding to
    Hasham Niaz Reply, if you want your query to use existing Index then you need to add your Primary Key column in the query and guide server for executing the query
    e.g
    Declare @I int
    SET @I = 0
    SELECT MAX(EntityDate) AS LastEntityDate, EntityName
    FROM TExtractWHERE ID_pk @IGROUP BY EntityNameOPTION (OPTIMIZE FOR (@I 0))Above query will replace your Cluster Index Scan by Cluster Index Seek and you will surely find some improvement in your query performance.

  • RELEVANCY SCORE 2.85

    DB:2.85:Compare 2 Columns Then Compare Row And Copy 7m


    Hello,
    I have an excel (2010)spreadsheet (.docx)that has sheet 1 and Columns A (name) and B (value), then sheet 2 columns A (name) and B (value). I need to find matches in Sheet1 A and Sheet 2 A then compare the B rows of each to find if the value in
    sheet 2 B is less than or greater than the value in sheet1 B for the unique column match.
    When this is complete i need to copy Column A matches and column B values of sheet 2 to Sheet 3 and a less than or greater than in Column C of sheet 3. In a perfect world i could color code Column B of sheet3 to be red for positive change, green for negative
    change and yellow for equal. However i will be happy with just about anything at this point.
    EX:
    Sheet 1 Sheet 2 Sheet 3
    A B A B
    A B C
    hi 1 my 3 my
    3 =
    bye 2 bye 1 bye
    1
    my 3 hi 2 hi
    2
    see 10

    DB:2.85:Compare 2 Columns Then Compare Row And Copy 7m

    Hi Ryan,
    If you don't like using VBA or Macros, you could use a simple formula, but to use this you must first sort the data on sheets 1 and 2 (say 'A-Z').
    On Sheet 3 iassuming row 1 has the headings 'Name and 'Value' or similar, put this formula in cell A2 .... =IF(Sheet1!A2,Sheet1!A2,IF(Sheet2!A2,Sheet2!A2,)) and drag down
    Then in cell B2 put this formula .... =IF(Sheet1!B2=,,IF(Sheet1!B2Sheet2!B2,,IF(Sheet1!B2=Sheet2!B2,=,))) and likewise drag down
    It worked for me so hope you understand this and get it to work.
    Cheers

  • RELEVANCY SCORE 2.85

    DB:2.85:Calculating Unique Values Based On Three Columns ca


    I have a large array of data, with multiple columns and I am trying to find all the unique records in one column which meet two other criteria.

    My data looks something like
    CriteriaA = OfficeX
    CriteriaB = StatusA
    ColumnOfficeColumnNameColumnStatus
    Can appearNeeduniquerecords fromCan
    Appear more than once
    more than oncethis column
    I have a forumla which finds unique values with two columns with onecriteria, but I need it to count two criteria.

    DB:2.85:Calculating Unique Values Based On Three Columns ca

    I know this is correct as little programming I do is correct, but it works
    Code triggers on the change of a pull down menu via this code

    Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = $C$8 Then
    Application.Run changeMacro
    End If
    End Sub

    and performs this:
    Sub changeMacro()

    Dim strSquadron As String
    Dim columnOffset As Long
    Dim strOverdueCount As Long
    Dim strCurrentName As String


    strSquadron = Range($C$8).Value

    columnOffset = 1

    Do While Sheets(TMAAA).Range(A columnOffset).Value strSquadron
    columnOffset = columnOffset 1
    Loop

    Do While Sheets(TMAAA).Range(A columnOffset).Value = strSquadron

    If IsEmpty(Sheets(TMAAA).Range(K columnOffset)) = True Then
    lngEmptyCell = lngEmptyCell 1
    columnOffset = columnOffset 1
    If lngEmptyCell 10 Then
    Exit Sub
    End If
    ElseIf Sheets(TMAAA).Range(K columnOffset).Value *OVDUE Then
    If Sheets(TMAAA).Range(K columnOffset).Value = *SCHED Then
    If Sheets(TMAAA).Range(B columnOffset).Value = strCurrentName Then
    columnOffset = columnOffset 1
    ElseIf Sheets(TMAAA).Range(B columnOffset).Value strCurrentName Then
    strCurrentName = Sheets(TMAAA).Range(B columnOffset).Value
    strOverdueCount = strOverdueCount 1
    columnOffset = columnOffset 1
    End If
    Else
    columnOffset = columnOffset 1
    End If
    ElseIf Sheets(TMAAA).Range(K columnOffset) = *OVDUE Then
    If Sheets(TMAAA).Range(B columnOffset) = strCurrentName Then
    columnOffset = columnOffset 1
    ElseIf Sheets(TMAAA).Range(B columnOffset) strCurrentName Then
    strCurrentName = Sheets(TMAAA).Range(B columnOffset).Value
    strOverdueCount = strOverdueCount 1
    columnOffset = columnOffset 1
    End If
    End If

    Loop

    Range($D$10).Value = strOverdueCount

    End Sub

  • RELEVANCY SCORE 2.85

    DB:2.85:Get Unique Value For A Range Of Values From A Table x3


    Please help to identify the sql to get unique value for following scenario

    Table A has 3 columns

    column1 column2 column3

    DB:2.85:Get Unique Value For A Range Of Values From A Table x3

    user7666373 wrote:
    Please help to identify the sql to get unique value for following scenarioUnique combinations of three columns? If so:

    select DISTINCT column1,column2,column3 from A;SY.

  • RELEVANCY SCORE 2.84

    DB:2.84:How To Find Combination Of Column Is Unique... as



    I have table with some columns with no primary key..
    i have to find the combination of columns which may the row unique...
    how to do thatilikemicrosoft

  • RELEVANCY SCORE 2.82

    DB:2.82:How To Compare The Names And Types Columns Of Two Tables? fz


    Assume I have two tables aaa and bbb

    How do I compare the columnnames and types of the two columns?

    It should not matter if one of them has e.g. an additional index.
    Furthermore it does not matter if the rows/row values differ.

    Peter

    DB:2.82:How To Compare The Names And Types Columns Of Two Tables? fz

    You have to use ALL_TAB_COLUMNS for this:

    SQL with t1 as
    2 (select column_name, data_type, data_length, data_precision, data_scale, nullable from all_tab_columns where table_name='EMP' and owner='MAXR'),
    3 t2 as
    4 (select column_name, data_type, data_length, data_precision, data_scale, nullable from all_tab_columns where table_name='EMP2' and owner='MAXR')
    5 Select a.column_name, a.data_type, decode(a.data_precision, null, '('||a.data_length||')', '('||a.data_precision||','||a.data_scale||')') length,
    6 b.column_name, b.data_type, decode(b.data_precision, null, '('||b.data_length||')', '('||b.data_precision||','||b.data_scale||')') length,
    7 a.nullable, b.nullable
    8 from t1 a full outer join t2 b on a.column_name=b.column_name;

    COLUMN_NAME DATA_TYPE LENGTH COLUMN_NAME DATA_TYPE LENGTH N N
    ------------ --------- ---------- ------------ --------- ---------- - -
    EMPNO NUMBER (4,0) EMPNO NUMBER (4,0) N N
    ENAME VARCHAR2 (10) ENAME VARCHAR2 (10) Y Y
    () NEWCOL NUMBER (22) Y
    MGR NUMBER (4,0) MGR VARCHAR2 (10) Y Y
    HIREDATE DATE (7) HIREDATE DATE (7) Y Y
    SAL NUMBER (7,2) SAL NUMBER (7,2) Y Y
    COMM NUMBER (7,2) COMM NUMBER (7,2) Y Y
    DEPTNO NUMBER (2,0) DEPTNO NUMBER (2,0) Y Y
    JOB VARCHAR2 (9) () Y

    9 rows selected.Max
    http://oracleitalia.wordpress.com

  • RELEVANCY SCORE 2.82

    DB:2.82:In A Grid, Remove Item From Combobox That Has Already Been Used In Another Row? 39


    funny title, isn't it? so, i'm using a Grid (not a DataGrid) to display / modify / insert /delete data. one of my columns contains a ComboBox. because of a unique key constraint in the DB-back end that actually stores the data, each row must have a unique ComboBox value. so what i want to do is either find some form of validation, OR just remove items from the list the ComboBox's ItemSource is bound to, so the item can't be selected twice. removing is pretty easy, but problems arise when the row is deleted, because in this case the previously deleted ComboBox value should be 'restored' to the ComboBox's ItemSource. again, this could be handled in the Click event of the delete button, but that's highly inconvenient. what i'm doing now is once the SelectionChanged event fires i'm iterating through every row, build an array of the ComboBoxes values, and compare them against the value that just got selected. not very elegant, huh? either way, any ideas to put me on the right track? Thanks, Tom

    DB:2.82:In A Grid, Remove Item From Combobox That Has Already Been Used In Another Row? 39

    We are changing the issue type to “General Discussion” because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to “Question” by opening the Options list at the top of the post window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions. Thank you!

  • RELEVANCY SCORE 2.82

    DB:2.82:How To Show Aggregated (Unique) List Column Item Values In A Ootb Chart Web Part sx


    Dear All,
    I would appreciate any help on the following topic.
    I have a simple custom list with 3 columns created. I connect the list web part with a Chart Web Part (The OOTBone in 2010) and it works fine. When I filter the list the grath is filtered as well.
    The problem is that I need to represent a count of unique values (based on one of the available text columns) in the chart per category. What happens instead is that all items in each column are taken as unique and no aggregationis donewhich
    spoils the whole graph idea.
    Is there a parameter settings in the Connection or in the Chart Web partthat should be used. If no, is there a simple (no coding) way to put this unique values count (per column) in additional columns and then to put it from there in the graph.
    So far I have not managed to find a way to take OOTB the aggregated categories (x axis) and depict them in a simple grapth...
    If no simple solution is available I would still appreciate a more complex one :).
    Thanks in advance,
    Kindest Regards
    TihoG

  • RELEVANCY SCORE 2.81

    DB:2.81:How To Insert Random Unique Values In 2 Columns ? d3


    I have table with 2 columns

    Create Table code_for_code (
    first_code varchar2(10) unique not null,
    second_code varchar2(10) unique not null );

    and i want to make PL/SQL code to insert random unique values in both columns , for instance 20 records ........ how can i do something like that ?

    DB:2.81:How To Insert Random Unique Values In 2 Columns ? d3

    John Spencer wrote:
    Blu:

    "Needless to say, I left that company to work for someone who actually listens to the developers when they point out that something is stupid or impossible."

    I don't believe you. There is no such company :-)There is! Took a long time finding one, but I'm here! :D

  • RELEVANCY SCORE 2.81

    DB:2.81:How To Compare Two Columns Of Two Worksheets . 98


    I have two worksheets. I want to compare a column of sheet A with a column of sheet B .

    than delete the rows from sheet Afor which the column values of worksheetB match with the value of worksheet A

    DB:2.81:How To Compare Two Columns Of Two Worksheets . 98

    Try :

    Sub test()
    Dim i As Long
    With Sheets("SheetA")
    For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Application.CountIf(Sheets("SheetB").Columns(1), .Cells(i, 1).Value) 0 Then
    .Cells(i, 1).EntireRow.Delete
    End If
    Next i
    End With
    End Sub

    Daniel

  • RELEVANCY SCORE 2.79

    DB:2.79:Formula Issue d7


    I want to compare if there are difference values with 2 columns.
    Please help.

    DB:2.79:Formula Issue d7

    Assuming the columns are A and B with a column header in row 1.
    Add a check column in C with the formula (entered in C2)
    =COUNTIF(A:A, B2)
    Copy this down to the bottom of your data set.
    This will tell you if there are no (0), one (1), or multiple (1) appearances of the relative value in column B in column A.
    If all values are equal to 1, your columns match.
    Highlight A1 to C1, go to Data Tab, click Filter. The filter can then be used to examine the results in column C.

  • RELEVANCY SCORE 2.78

    DB:2.78:Align Two Report Sheets zx



    I am a newby. I have a two page report with variables and values with columns and rows arranged one way, I have another report sheet generated with the same data with columns and values arranged in a different order.

    To compare these worksheets (Both text files) What is the optimal SAS procedure? I assume I should not use PROC compare but use either transpose or some by statement?

    Any suggestions.

    Thanks

    DB:2.78:Align Two Report Sheets zx


    if you can give a layout of the variables and dummy values we can perform joins and then use proc freq.

  • RELEVANCY SCORE 2.78

    DB:2.78:Compare 2 Columns Display Unique Values aj



    Hi,

    I have a file with 2 non-identical columns: A B. Each entry in Column A has an entry somewhere in Column B. I want to display a list of entries that will compare all the entries in A with those in B and only display those that are missing from B.

    In Excel this works:

    =IF(COUNTIF(B:B,A2)=0,A2,"")

    I have attached a sheet containing some dummy data and the excel formula working correctly.

    Any help would be greatly appreciated...

    Thanks

    A

    DB:2.78:Compare 2 Columns Display Unique Values aj


    Hi Celambarasan,

    Thanks very much for your prompt response.

    Would you mind specifying how this would be incorporated into the script?

    I am very new to Qlikview...

    Cheers

    Alan

  • RELEVANCY SCORE 2.77

    DB:2.77:User Compare kj



    How can I do user compare for 2 users to find out the unique roles. also let me know the table name for this user compare (Roles level)

    Thx,

    Lisa

    DB:2.77:User Compare kj


    Go to SUIM

    Select User by complex selection selection criteria

    Execute(F8)

    Enter 2 user idu2019s in user tab

    Execute

    Select 2idu2019s

    Select roles tab on top

    Then sort or download in to excel and sort unique records

    Go to se16

    Enter table name AGR_USERS

    Execute/enter

    Enter user idu2019s

    Execute

    If there are 2 entries (Repeted entries)then that is common role for both users

    download in to excel and sort unique records

  • RELEVANCY SCORE 2.77

    DB:2.77:Compare Two Columns mk


    Hi,

    DB:2.77:Compare Two Columns mk

    Hi,

    If you are using Excel 2007 or a higher version, then that option should be there.

  • RELEVANCY SCORE 2.76

    DB:2.76:Comparing Columns 3k


    Hey guys,

    I'm trying to compare the values in 2 columns and return the value in a third column.

    If column B column c then return value in column a.

    I would use nested if statements but there are 100 cells to compare and I don't feel like typing that much.

    DB:2.76:Comparing Columns 3k

    OK

    With the complementary infos, the problem is clearly defined.

    The response also.
    Given the described rules we can't do what you asked.

    Numbers is not C++, not Fortran, not Excel.
    It doesn't offer functions dealing with arrays or matrix.

    Of course we may get the wanted result but not with your rules.

    Here, row 1 is an header one
    in cell B1 I entered:
    =IF(ISBLANK(A),"",ROUND(A*$F$1,2))

    in cell C1 I entered:
    =IF(ISBLANK(A),"",(A*$F$2)+(0.5*$F$3*A^2))

    in cell D1 I entered:
    =IF(OR(ISBLANK(C),C=0,CB),"",A)

    in F1 I entered 124.667
    in F2 I entered 0
    in F3 I entered 3

    which are the constants used by your sheet.

    In D54 (row 54 is a footer row) the ormula is
    =MIN(D)

    Yvan KOENIG (from FRANCE dimanche 19 octobre 2008 12:42:46)

  • RELEVANCY SCORE 2.76

    DB:2.76:How To Find Unique Values In An Array s1


    how to find unique values in an array. Is there some method for the same?

    rakesh

  • RELEVANCY SCORE 2.75

    DB:2.75:Re: Fuzzy Compare Of Columns f1


    SOUNDEX?

    DB:2.75:Re: Fuzzy Compare Of Columns f1

    Doesn't look that it would fit OP requirments though.
    Or am I doing something wrong here?:No, you're doing it right.
    The number is between 0 and 100 and represents a "similarity" value between the two. It's up to the person using this to determine what their threshold is that would indicate that two values are similar enough to be determined as the same and then up to them which of the values they take based on some business rule or other.

    I have used it for matching addresses stored in a single field (old badly designed database) to a cleansed version of the same address and to a structured address linked to the same record to determine which of the two addresses (cleansed or structured) provides the "best" information.

    Of course there was an exercise of reviewing the output and determining what the threshold was and then flagging up records that just didn't meet the necessary criteria so they could be manually fixed.

  • RELEVANCY SCORE 2.75

    DB:2.75:Finding Common Values In Columns Of Data pk


    Using Excel 2007

    Assume I have 10 columns, each containing both unique and common alphnumeric data

    What I would like to be able to do is:

    Identify all the variables that are common to
    all 10 (or whatever number) of columns.

    Many thanks for your help.

    DB:2.75:Finding Common Values In Columns Of Data pk

    Thank you for all your help on this.

    I've tried this out and I can see it works but I'm experimenting with various other workarounds but you've been a great help in getting me to clarify my thinking and try out various options.

  • RELEVANCY SCORE 2.75

    DB:2.75:Subtotal Of Unique Values sd


    I have4 columns of data (alpha numeric). I would likethe sum of unique valuesat the top of each column and also be able to filter and get the subtotal of the unique values.

    DB:2.75:Subtotal Of Unique Values sd

    Thank you for your answer.

    You're welcome!
    --
    Biff
    Microsoft Excel MVP

  • RELEVANCY SCORE 2.75

    DB:2.75:Find And Drop Duplicate Rows mz



    I have 2462 rows by 13 columns. Some of these rows contain duplicate values in 12 of the 13 columns. The column that always contains unique text is called PK.SAMPLE. How can I find these rows of almost exact duplicates and remove them from the data set?

    Thanks,

    mgm

    DB:2.75:Find And Drop Duplicate Rows mz


    I wrote a script recently to check to make sure I wasn't adding duplicate rows to a data table. Maybe this will help.

    The script iterates through all the rows in the table (i counter) and each of my 9 columns (k counter) comparing the values to those in the last row. If it finds any rows that are equal, it will delete the last row.

    Hope that makes sense. You would have to modify it to look for any duplicates, but the script should still be pretty straightforward.

    There are issues displaying my script.

    Message was edited by: MJaworski

  • RELEVANCY SCORE 2.75

    DB:2.75:Find Empty Columns In A Table sd


    Does anyone have a trick to find columns in a table where all the values are null? TIA.

    DB:2.75:Find Empty Columns In A Table sd

    Thanks Andre, your solution worked well.

  • RELEVANCY SCORE 2.75

    DB:2.75:Is There An Easy Way To Compare Two Null Columns? 7m


    I need to compare a significant number of columns between records in two tables for a data conversion. I really need a comparison that will return true if: 1) both columns are null; or 2) both columns are not null and equal. I want it to return false if: 3) one column is null and the other is not; or 4) both columns are not null and are not equal. I am trying to find records which are not exact matches.

    I found documentation at oracle-base.com about the SYS_OP_MAP_NONNULL function that would do what I want, but I don't want to use this since it's an undocumented feature and my code will be in production for a period of time.

    I would rather not have to use a construct like this for each and every column I'm comparing:

    (
    (
    a.col is null
    and b.col is null
    )
    or (
    a.col = b.col
    )
    )Also, I know about the NVL function, but I'm comparing columns which are entered by users, and I'm not comfortable substituting any values for null because those values might actually exist in the data.

    DB:2.75:Is There An Easy Way To Compare Two Null Columns? 7m


    For what it's worth, I compared explain plans using both methods and they were 100% identical.

    Thanks for posting that result. That may clear up any doubt others may have had.

  • RELEVANCY SCORE 2.74

    DB:2.74:Need To Find Hierarchy Level k3



    Hi Guys,

    I need your help on finding hierarchy level. I have data and hierarchy level columns. First I want to take unique data(numbers only and the values which started with text (D_12) not needed i.e 1000 like that). Next, I want to find higher node (by mapping with input sheet) with the unique values (Store as Hierarchy1). Now, there might be chance that I have another higher node for hierarchy1. So again I want to map the hierarchy1 with input sheet. Like wise I have to find upto the top level. I have attached sample excel (Input file which is raw data and I want output like showed in output sheet). Can any one suggest idea on how to do this.

    Thank you in advance.

    Kind Regards,

    Tamil

    DB:2.74:Need To Find Hierarchy Level k3


    Hi All,

    I have found the solution and it's just 1 line of code.

    Hierarchy (Node,Parent,Name)

    LOAD Node,

    Parent,

    Node as Name

    FROM

    [New Samp.XLSX]

    (ooxml, embedded labels, table is Input);

    Thanks to Marco, Ishfaque and Mario.

    Mario: Can you attach the qvw file again. I would like to see your solution

    Kind Regards,

    Tamil

  • RELEVANCY SCORE 2.74

    DB:2.74:Vba Compare Id Of Two Sheets And Add Values Of Sheet2 Into Sheet1 With New Columns From Row Values (Sheet2 Column B) fs


    Hello,
    I don't know how to solve my problem:
    sheet1: columns:

    ID
    Type
    Name
    Documentation

    sheet2: columns:

    ID
    Key
    Value

    sheet1_ID is unique 1:m sheet2_ID - sheet2_Key cell.value should be the new sheet1_columnName with the cell.value of sheet2_value cell.value
    and advanced: most of the sheet2_key will be only unique if sheet1_ID = sheet2_ID but but it should not insert double sheet1_columnName
    I will need it because I have 2500 rows to match and add.
    I have a array which compares the ID's but don't know how to take the value of sheet2_key and add a new column in sheet1_columnName (key.cell.value) if it is not created
    --------------------------------------------------------
    Sub compare()
    Dim i As Integer
    Dim t2_zeilen As Integer

    t2_zeilen = 2170

    For i = 1 To t2_zeilen
    With Sheets(1).Range(A1:A925)
    Set suche = .Find(Sheets(2).Range(A i).Value, LookIn:=xlValues)
    Set t1_leer = Range(A1:A65536).Find(, LookIn:=xlValues)
    If Not suche Is Nothing Then
    suche.Select
    Sheets(1).Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 20)).Value = _
    Sheets(2).Range(B i, U i).Value
    Else
    t1_leer.Select
    ActiveCell.Value = Sheets(2).Range(A i).Value
    Sheets(1).Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 20)).Value = _
    Sheets(2).Range(B i, U i).Value
    End If
    End With
    Next i

    End Sub

    DB:2.74:Vba Compare Id Of Two Sheets And Add Values Of Sheet2 Into Sheet1 With New Columns From Row Values (Sheet2 Column B) fs

    I have a array which compares the ID's but don't know how to take the value of sheet2_key and add a new column in sheet1_columnName (key.cell.value) if it is not created

    I
    do not think I understand that. Anyway have a look at the code below.

    BTW, here is the German version of this forum:
    https://social.msdn.microsoft.com/Forums/de-DE/home?forum=officede
    Andreas.
    Option Explicit

    Sub Test()
    Dim All As Range
    Dim ThisID As Range, FoundID As Range

    With Sheets(1)
    'for each ID in this sheet
    For Each ThisID In .Range(A1, .Range(A Rows.Count).End(xlUp))
    'search in the other sheet
    Set FoundID = Sheets(2).Range(A:A).Find(ThisID.Value, LookIn:=xlValues, LookAt:=xlWhole)
    'Found?
    If Not FoundID Is Nothing Then
    'Combine the cells
    If All Is Nothing Then
    Set All = FoundID
    Else
    Set All = Union(All, FoundID)
    End If
    End If
    Next
    'Something to do?
    If All Is Nothing Then Exit Sub
    'Copy column B:C from the results to column E in this sheet
    Intersect(All.EntireRow, Sheets(2).Range(B:C)).Copy .Range(E1)
    End With
    End Sub

  • RELEVANCY SCORE 2.74

    DB:2.74:Excel Vba - Find And Select Columns Matching A List Of Numbers dk


    Hello, I am a beginner with Excel VBA and would really be thankful if someone could help me. I have a CSV file with the following information: Column A, Row 1: is called Bin Any other columns in Row 1, are hosting a unique numeric value for the Bin (i.e. 124, 240, 356, 410, etc.) From one CSV file to another, these numeric values are not systematically stored in the same columns. The only constancy is the location of the Bin cell which is always in Column A, Row 1. Hence, I need to write a VBA macro to select the columns hosting a list of numeric values (124, 240; including column A) and hide all other columns which numeric values do not match my list. I figured that I need to use an array to declare my list of values and to use Union to select the matching columns. I just don't know how to put the macro together. Thanks for helping, Warm regards,

    DB:2.74:Excel Vba - Find And Select Columns Matching A List Of Numbers dk

    I have some code I wrote which works great. But I need help writing a macro to print each part number on a seperate page.
    After the operator opens the page they simply click Open 311795 Orangeburg Report which imports the data in to the excel sheet as you see below. It sorts through the data look at the part number in Column A. If the same part number has two different ship
    dates it inserts two rows between the same part number. If a different part number appears it inserts 7 blank rows. All this works great. Now I want to add another button that is currently labeled Print Selected Area (For now). I want the Macro to look at
    the part number in Column A, then if the part number matches to print it to a single page.
    For example A3 A7 Part Numbers Match, So I would like to print the data in A3:G7 and issue a activeSheet.PrintArea to print this area. Then the code sees that only G15 has the only part number So print A15:G15, then A23:A35 have the same part number
    so print A23:G35 and continue on through the entire sheet. There is always 7 blanks rows between part number, so if the code sees more then 7 blank rows stop the code printing is finished. Depending on the customer this sheet could have 3600 rows of data or
    as little as 300 rows.

  • RELEVANCY SCORE 2.74

    DB:2.74:If Statement To Compare. p9


    Hi All i have 2 local variables
    x and y values x = 5 and y= 10
    and table abc columns A and B .
    i want to compare the values x and y with values in table .
    something like
    if x between A and B or Y between A and B
    (Select ' The values already exist' )
    Can any1 please provide with a valid syntax for such requirement where i compare the values in the table with local variables.
    Thanks in Advance
    Priya

    DB:2.74:If Statement To Compare. p9

    Hi Visakh16 ,
    Your always a life Savior in complex situations . thank you :)

  • RELEVANCY SCORE 2.74

    DB:2.74:Problem With Merge Statement The First Time, Please Help!!! mx


    I have a table with 3 columns [Id (primary key), StoreId int, CreateDate datetime]
    I am doing a Merge to that table (target table) from another (source table) that is a temporal table #TempTable, the temporal table has two columns [StoreId int, CreateDate datetime]
    This is the simple code:
    Merge into MyTable as T
    using #TempTable as S on (T.StoreId = S.StoreId and T.CreateDate = S.CreateDate) -- this combination is my join needs to be unique
    When Not Matched Then
    Insert(StoreId, CreateDate)
    values(S.StoreId,S.CreateDate )

    the problem is that the first time, when the target table is empty, is like the merge does not work, because it insert the duplicated values that comes in the source table.
    Does the merge insert row by row and compare at the same time or just insert the whole set of values and if the table is empty does not have anything to compare??????
    If is like that what can I do for that problem the first time?????? Thanks in advance
    Example: Source table StoreId CreateDate
    1
    2012-04-03 10:40:08.000 ---- This row is duplicated so can not enter in my target table
    1
    2012-04-03 10:32:18.000
    12012-04-03
    10:34:30.000
    12012-04-03
    10:40:08.000

    DB:2.74:Problem With Merge Statement The First Time, Please Help!!! mx

    You are right Tom, i was wrong thinking on the merge, i know SQL does things in sets, but i was confused with the merge, maybe because i have always work with tables that already have records.
    Thanks very much to all!!!!
    I do alsoprefer the merge statement instead the other insert, but good to know, thanks to all!!!!

  • RELEVANCY SCORE 2.73

    DB:2.73:Compare Unique Index xs


    Hello all,
    We have a archiving process that is trying to insert data from current to archive table. The process failed because it could not insert duplicate index key value for a unique non-clustered index. The unique nonclustered index exists in both current and archive
    table on col1, col2 and col3. So, how can I check which unique values are already there in archive table? Will inner join on those columns give the result?
    Thanks.

    DB:2.73:Compare Unique Index xs

    Lets say each of these tables has two columns Id and Name.Hope this helps:
    MERGE ArchiveTable AS T
    USING (SELECT * FROM CurrentTable) AS B(Id, [Name])
    ON T.Id = B.Id
    WHEN NOT MATCHED THEN INSERT (Id, [Name]) Values(B.Id, B.[Name]);

    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

  • RELEVANCY SCORE 2.73

    DB:2.73:These Columns Dont Currently Have Unique Values. 3s


    I keep getting this error:
    These columns don't currently have unique values.
    Line 955:            DataRelation relation2 = new DataRelation(STOCK_CAT_2, ds.Tables[Cat1].Columns[LEVEL1], ds.Tables[Items].Columns[CAT_LEVEL_2]);Line 956:            ds.Relations.Add(relation2);
    I have this categories table
    this is the parent dataset, only the first field has unique ids,
    216 1 Champagne             1 3 182 213 T217 1 NZ Sparkling Wine  1 3 182 213 T 218 1 Other White              1 3 182 213 T 219 1 NZ Sparkling            1 3 182 213 T 220 1 Dessert                     1 3 182 213 T221 1 Fortified Wines         1 3 182 213 T
    the matching related fields on the items dataset has no unique ids
    does one of the colunms must be unique, can't be a many to may relation?
     
     
     

    DB:2.73:These Columns Dont Currently Have Unique Values. 3s

    I had this same problem.  What I found was that I was using aliases in the sql query and I had forgotten to change the column names in the relation to the alias for the column.  This can also happen if you have made a mistake naming a column when you instantiate a primary key data column object.
     
    Example:
     
    rampSQL = SELECT job as Job,revision as Rev,ramplevel as [Ramp Level],feedrate as [Feed Rate], + _
    extruder_rpm as RPM,cutter_speed as [Pelletizer RPM], + _
    torque_increment [TQ% Change] ,psi_increment as [Pressure Change] + _
    FROM RampProfile
    RampDA = New SqlDataAdapter(rampSQL, WOConn)
     
    RampPKDC = New DataColumn() {RecipeManager.Tables(RampProfile).Columns(Job), _
    RecipeManager.Tables(RampProfile).Columns(Rev), _
    RecipeManager.Tables(RampProfile).Columns(Ramp Level)}
     
    RecipeManager.Tables(RampProfile).PrimaryKey = RampPKDC
     
    rampFKDC = New DataColumn() {RecipeManager.Tables(RampProfile).Columns(Job), _
    RecipeManager.Tables(RampProfile).Columns(Rev)}
     
    WORampDR = New DataRelation(FK_WO_ramp, WOPKDC, rampFKDC)
     
    RecipeManager.Relations.Add(WORampDR)
     
    I hope this helps.

  • RELEVANCY SCORE 2.73

    DB:2.73:Unique Values And Dates 8f


    I have a large spreadsheet. One of the columns (C)contains ID numbers. Another column (G) has dates (mm/dd/yyyy and time portion). I am trying to find unique ID numbers from Column G falling above or below a specific date (10/01/2010). Here is the formula
    I am using:
    =SUM(--(FREQUENCY(IF(G1130:G1181"10/01/2010",MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"ROWS(C1130:C1181))))0))

    DB:2.73:Unique Values And Dates 8f

    Thank you! I figured it out!

    Ok, good deal. Thanks for the feedback!
    --
    Biff
    Microsoft Excel MVP

  • RELEVANCY SCORE 2.73

    DB:2.73:Help To With Checksum pj


    i have been using checksum to compare any change in column values.
    i am comparing the checksum value for 3 columns.
    I am getting lot of duplicates.
    can i use hashbytes to compare column values. If not what are the alternative. Please suggest

    DB:2.73:Help To With Checksum pj

    Hi reddy335,

    If you are using SSIS, please take a look at the similar thread about this topic: Unique Row ID based on all columns - HashByte or Checksum?
    Hope this helps.
    Best Regards,
    Stephanie Lv

  • RELEVANCY SCORE 2.73

    DB:2.73:Compare Two Date Columns 88


    Hi

    I want to compare two dates columns in one table.

    Here I want to confirm both values are same in every record.

    Hope this make sense

    Cheers

    Shabar

    DB:2.73:Compare Two Date Columns 88

    Hi, Shabar,

    If d1 and d2 are the two DATE columns:
    SELECT*-- or list whatever columns you want to see
    FROMtable_x
    WHEREd1 != d2
    ;will show all rows where they are both entered, but not the same.

    If you want to include rows where one of them is NULL, but the other is not:
    SELECT*-- or list whatever columsn\ you want to see
    FROMtable_x
    WHERELNNVL (d1 = d2)
    ;

  • RELEVANCY SCORE 2.73

    DB:2.73:Compare Data In Two Columns And Conditionally Format 8m



    Post Author: foghat

    CA Forum: General

    Hey all, I am trying to compare data in 2 different columns - when the values are not equal I want to highlight the entire row, or even just one of the data fields. Anyone have any thoughts?Thanks.

    DB:2.73:Compare Data In Two Columns And Conditionally Format 8m


    Post Author: foghat

    CA Forum: General

    Thanks guys, I had tried something similar before posting and kept getting a 'number is required here' error - I had figured that meant I could only compare a data field against a literal number. Turns out I was getting that error because the two data fields I am trying to compare are different formats. Of course that was easily remedied and things are working like a charm now.

  • RELEVANCY SCORE 2.73

    DB:2.73:Unique Constraint pz


    What does this mean that because of the search mechanism for unique constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.

    DB:2.73:Unique Constraint pz

    thanx kamal u solved my problem again....

  • RELEVANCY SCORE 2.72

    DB:2.72:Analytic Function? kk


    Hello everyone,

    I tried to figure out how to do a query that will return me the result 2 for theses values:

    Create table Mytable (A int, B varchar2(10), C varchar2(10));
    Create table Yourtable (A int, B varchar2(10), C varchar2(10));

    INSERT INTO MyTable (A, B,C) Values (1, 'A', 'B');
    INSERT INTO MyTable (A, B,C) Values (2, 'A', 'B');
    INSERT INTO MyTable (A, B,C) Values (3, 'A', 'C');

    INSERT INTO YourTable (A, B,C) Values (4, 'A', 'B');
    INSERT INTO YourTable (A, B,C) Values (5, 'C', 'B');
    INSERT INTO YourTable (A, B,C) Values (6, 'A', 'C');

    In fact, I want to have the result 2 because:

    - I want to compare columns B and C of the "first" row of MyTable with the columns B and C of the "first" row of YourTable.
    - Then I want to compare columns B and C of the "second" row of MyTable with the columns B and C of the "second" row of YourTable.
    - ...

    The ordering of the rows in both table is done by the unique column A.

    I first tried to use RANK() but I am not able to reference an alias of this "column" outside the query that contain it.

    Example:

    SQL select a, b, c, RANK() OVER (ORDER BY a) "rank" from MyTable;

    A B C rank
    ---------- ---------- ---------- ----------
    1 A B 1
    2 A B 2
    3 A C 3

    It work... But:

    SQL select rank from (select a, b, c, RANK() OVER (ORDER BY a) "rank" from MyTable);
    select rank from (select a, b, c, RANK() OVER (ORDER BY a) "rank" from MyTable)
    *
    ERREUR la ligne 1 :
    ORA-00904: "RANK": invalid identifier

    Does not work.

    Maybe it is simple but I don't see it the rigth way...

    Best regards.

    Carl

    DB:2.72:Analytic Function? kk

    As simple as that... :(

    Thanks a lot.

    Carl

  • RELEVANCY SCORE 2.72

    DB:2.72:Need To Compare Lists. 9j


    I need to compare lists of text data (.numbers or .csv) to find duplicate or unique entries. I can't find anything in Numbers or other tool db online. Any ideas? I don't want to spend much $$$.

    DB:2.72:Need To Compare Lists. 9j

    Thanks for the feedback. I think most of the credit should go to Badunit in this case. If you follow this forum area you will see the Countif function used in this way quite often. With a little more complexity it's possible to pull out a list of distinct strings in a summary table, but as you have seen, a combination of simple formulas and a little sorting, deleting and copying you can get there pretty quickly.Jerry

  • RELEVANCY SCORE 2.72

    DB:2.72:Plsql Procedure Compare Two Columns am


    Hi Friends,
    i have THREE Tables , TABLE A has two columns X1 Y1 , Table B has X2 Y2 AND TABLE C WITH Z1 column . i want a procedure which compare COLUMNS A.Y1 and B.Y2 columns ,if column Y1 is diffrent than Y2(VALUE WILL BE CHARCHTERS like yes,NO,null) then i have to insert the same value of Y1 t0 table C OF COLUMN Z1 .if the values are same ignore .

    DB:2.72:Plsql Procedure Compare Two Columns am

    Hi,

    Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    In the case of a DML problem, like this, the INSERT statements should populate the tables as they exist before the changes, and the results will be the contents of the changed table (c) after the changes.
    It never hurts to say what version of Oracle you're using.

    799424 wrote:
    Hi Friends,
    i have THREE Tables , TABLE A has two columns X1 Y1 , Table B has X2 Y2 AND TABLE C WITH Z1 column . i want a procedure which compare COLUMNS A.Y1 and B.Y2 columns ,From which rows? Do you mean you only want values of a.y1 that are different from any value of b.y2, or do you mean just for some related rows? If the latter, what rows are related? Do a.x1 and b.x2 have something to do with this?

    if column Y1 is diffrent than Y2(VALUE WILL BE CHARCHTERS like yes,NO,null) then i have to insert the same value of Y1 t0 table C OF COLUMN Z1 .if the values are same ignore .If one of the vlaues is NULL and the other is not, do you want to consider thme the same?
    What if both are NULL?

    Depending on your answers, you might want something like this:
    INSERT INTO c (z1)
    SELECT a.y1
    FROMa
    JOINbONa.xi= b.x2
    WHERELNNVL (a.y1 = b.y2)
    ;

  • RELEVANCY SCORE 2.72

    DB:2.72:How To Access Cell Of m9



    Hi,I have a "h:dataTable" and want to loop over it to find a specific item.The table contains four columns: ID ("h:outputText"), a "h:outputText" field with id = "name", and two forms (one with an edit link, the other with a delete link).I loop over the table and compare the "name" values:
    HtmlDataTable table = (HtmlDataTable) this.server.findComponent("mytable");
    for (int iRow = 0; iRow table.getRowCount(); iRow++)
    {
    Object objName = this.server.getComponentValue("mytable:" + iRow + ":name");
    }

    DB:2.72:How To Access Cell Of m9


    Hi Stan,I ran my sample on Tomcat 6 (including the MyFaces implementation of your "jboss-jsfunit-examples-ajax4jsf" sample). Here it worked fine, same for the modified NestedTable test. So maybe it is a problem with the Sun RI?Did you try to run the sample on JBoss 5 using the server default JSF implementation?Best regardsWolfgang

  • RELEVANCY SCORE 2.72

    DB:2.72:Proc Compare 8m



    Can someone please give me some advice?

    I have two datasets that contain unique records based on a unique key (record key). Data from one may not be in the other or the unique key may be there but only some value may have changed. I have three values to compare (reckey, caseload, and casedesc). I need to find all observations from the base dataset that no longer exist in the compare dataset as well as those new in the compare dataset that may not exist in the base dataset. I also need to find all observations where the observation exists in both datasets but some combination of the three values has changed.

    I have experimented with proc compare but can't get it to give me what I want. I'd like a simple list of all observations that are either different or non-matched in either dataset. I don't need the summaries, just the actual observations.

    I am hoping to use this as a learning experience with proc compare and I'm hoping someone can point me in the right direction.

    Thank you.

    DB:2.72:Proc Compare 8m


    Proc compare is not the best tool for indentifying miss matches. It is useful for looking for changes in observations that are in both datasets.

    If you just have a couple of variables to compare then just do it yourself in a data step.

    data check;
    merge one (in=in1 rename=(caseload=caseload1 casedesc=casedesc1))
    two (in=in2 rename=(caseload=caseload2 casedesc=casedesc2))
    ;
    by reckey ;
    length match $50 ;
    if not in1 then match='IN ONE ONLY';
    else if not in2 then match='IN TWO ONLY';
    else do;
    if caseload1 ne caseload2 then match='CASELOAD MISMATCH';
    if casedesc1 ne casedesc2 then match=catx(', ',match,'CASEDESC MISMATCH));
    end;
    run;

  • RELEVANCY SCORE 2.72

    DB:2.72:Find Unique Values In Column c3


    I have a worksheet with numbers in column A. What I have been trying to do is delete all the duplicate numbers and only leave the
    unique numbers. Below is the code I have works somewhat. It does delete some of the duplicate numbers but not all of them. Originally, the numbers came from two worksheets with the numbers in column A of both worksheets. I thought the best way would
    be to copy the numbers to one worksheet and delete the duplicates (code below).

    If there is a way to compare the numbers on both sheets (sheet1 and sheet2) numbers in column A without having to copy the contents of each, and list the unique numbers on another sheet (currently named "Unique"), I would love to see how that works.

    Thanks for your help. It is greatly appreciated.

    Public Sub findUniqueValues()

    Dim LastRow As Long
    Dim ws As Worksheet
    Dim dupes As Worksheet
    Dim Unique As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("Discrepancies")
    Set dupes = ThisWorkbook.Sheets("Dupes")
    Set Unique = ThisWorkbook.Sheets("Unique")

    ws.AutoFilterMode = False

    LastRow = ws.Range("A" ws.Rows.Count).End(xlUp).Row

    ws.Range("B1").Formula = "=COUNTIF(A$1:A$" LastRow ", A1)"
    ws.Range("B1").Copy ws.Range("B2:B" LastRow)

    Set rng = ws.Range("A1:B" LastRow)
    With rng
    ' find dupes
    .AutoFilter , Field:=2, Criteria1:="1"
    ' copy them to our dupes sheet
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy dupes.Range("A1")
    ' find unique
    .AutoFilter , Field:=2, Criteria1:=1
    ' copy them to our unique sheet
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Unique.Range("A1")
    End With
    ws.AutoFilterMode = False

    End Sub

    DB:2.72:Find Unique Values In Column c3

    I am not good in VBA, someone will definately respond, it should be very small fix.

    Try to use same code on a list with normal formatting with same font and whether its working. If yes, then I believe normal formatting and conditional formatting are different, so instead of rcell.Interiod.colorindex, you must also mention cell with conditional
    formatting.

  • RELEVANCY SCORE 2.71

    DB:2.71:Compare Data Between Two Tables jk


    Hey Experts
    I am having two tables both are having same structure.
    Both r having 210 columns

    I want to compare data between these two tables.

    I used follwoing query

    select * from t1
    MINUS
    select * from t2but even if thr is diff in 1columns .. i need to search 210 columns which is the exact columns and data

    how can i find the exact columns out of these 210 columns ?

    DB:2.71:Compare Data Between Two Tables jk

    You Can make use of Data Dictionary tables like User_columns or all_columns.

  • RELEVANCY SCORE 2.71

    DB:2.71:Simple Question On Join 9p


    hi,

    I have two similar tables .Table A and TableA_Backup. TableA_backup is the backup table.

    I truncated Table A to load new date. When I compare the counts of Table A and backup table there is difference in counts. How do I find out which records is missing in the TableA for the new load. We dont have unique column on these tables. So all the columns need to be joined.I have like 6 columns.

    DB:2.71:Simple Question On Join 9p

    In addition to Abdallah's response take a look http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#4for 6 methods listed.Premature optimization is the root of all evil in programming. (c) by Donald Knuth
    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

  • RELEVANCY SCORE 2.71

    DB:2.71:How Do I Compare Multiple Columns j9


    Hi,

    I want to compare a list with two columns to another list with two columns and find duplicates.

    For example,

    a
    b
    c
    d
    e
    f

    id
    name

    id
    name
    results

    123
    smith

    456
    smith
    no

    456
    jones

    987
    smith
    yes

    789
    jones

    123
    smith
    yes

    987
    smith

    132
    chen
    no

    654
    chen

    321
    chen

    I want to compare columns d and e to columns a and b and show either a yes or no in column f.

    Thanks

    DB:2.71:How Do I Compare Multiple Columns j9

    awesome. works great. thank you very much.

  • RELEVANCY SCORE 2.71

    DB:2.71:Merge Join (In Data Flow) Wont Match All Items zk


    Using:
    VS 2005 (?32-Bit?) on Win7 Pro 64-Bit
    SSIS Designer version 9.00.5000.00
    Connecting to SQL Server 2005

    Hello all,
    More and more often, we have clients that send us data with string/text columns that have a limited number of unique values. When I'm setting up the project and come across this, I convert the string/text columns to lookup tables.
    Then when I'm designing the SSIS Package, I have 2 data flow tasks. First task scans through each file looking for new unique values and adds them to the lookup tables before the file is imported (second data flow task).
    In order to find new lookup values, first data flow task's process is as follows:

    OLE DB Source connected to the source files (using XLSX files in this case).Aggregate Transformation to get unique values for each text columnDerived Column Transformation to convert to DT_STR (since I'm using XLSX files as source and text is stored as unicode in XL file, but I store text as varchar in SQL Server)Sort TransformationMerge Join Transformation (full outer join with the same varchar column from thecorrespondinglookup table the new values will eventually be added to)Conditional Split Transformation to weed out existing valuesOLE DB Destination to add new values to lookup table in SQL Server
    My diagram can be seen here: https://skydrive.live.com/redir?resid=5FEBB0EE89579911!599authkey=!AP4MJ4KnU9G5GPw
    I've done this set up many times in the past with text files with no problems. In the last few months when working with XL files, I've had problems with the merge join transform matching every single entry from the lookup table to the same entry in
    the incoming file. (I don't recall if old XL files (xls) along with the new XL files (xlsx) cause problems too...I don't recall if I've come across this issue working with text files either). When merge join can't match identical entries, lookup
    table ends up with duplicate entries.
    With this current project (can be seen in the screen shot linked to above), I have a lookup table with 120 records. The incoming XL sheet has 120 unique values (this exact same XL sheet was used to create the lookup table). Merge join can not
    match 8 of the 120 records and wants to reimport them causing duplicates.
    When I add a data viewer on the output of merge join showing the column from the lookup table (existing) beside the column from the incoming file (possible new), all but 8 records match, 8 possible new's have null existing's, and 8 new rows with exact same
    values in existing with null possible new's. When I copy this data from data viewer into a blank XL sheet and compare them (formula for cell C121: =B121=A106 filled down 8 rows), they all come back as TRUE (matching). (I don't think it is wise
    for me to post my actual data for confidentiality reasons, and I don't think I can dummy up some examples because there just doesn't seem to be a pattern).
    Has anyone run across this working with XLSX files? Is converting from unicode/DT_WSTR/nvarchar to ansi/DT_STR/varchar causing problems (if so, why can it match 95% and not match a select few)? Anyone have any ideas?
    Thanks for any help anyone can provide,
    CTB

    DB:2.71:Merge Join (In Data Flow) Wont Match All Items zk

    if there is no casing issue...what about datatypes or spacing?
    you could change the join in a full outer join and then add a dataviewer behind it to see where the problem is...Please mark the post as answered if it answers your question | My SSIS Blog:
    http://microsoft-ssis.blogspot.com |
    Twitter

  • RELEVANCY SCORE 2.71

    DB:2.71:Unique Key 3m


    I have a table like emp with columns (sl_no, emp_id, emp_name,dept_id).Serial number should be unique. And all the other values can be repeated. But I shouldn't have same values again for all the three columns.

    i.e. this can be allowed
    1, 5000, Emp1, Dept1
    2, 5000, Emp1, Dept2this shouldn't be allowed
    1, 5000, Emp1, Dept1
    2, 5000, Emp1, Dept1Do I have any other option other than the below code.
    ALTER TABLE emp
    add CONSTRAINT emp_unq UNIQUE (emp_id,emp_name,dept_id);If No, then Can I use this for 30 columns..?

  • RELEVANCY SCORE 2.71

    DB:2.71:Proc Compare Question dx



    Hello Everyone,

    I use proc compare to compare two sas datasets. As a result, proc compare identified a few rows having columns with unequal values. But when i browse the two datasets and the 'unequal' columns/values side by side the values in question look identical to me. I have pasted one of the 'unequal' columns/vlaues so you can see the actual message generated by proc compare. The column is defined as Type number, Length 8, Format Dollar21.2, and Informat Dollar21.2. in both datasets. Why does proc compare think they are different? Thank you for any input anyone may have.

    Sale_Price Base Sale_Price Compare Diff. %Diff

    $31004.85 $31004.85 3.638E-12 1.173E-14

    DB:2.71:Proc Compare Question dx


    Thanks Kevin.

    A further suggestion: drop the RELATIVE parameter value for the CRITERION option and go with ABSOLUTE instead, owing to how close to 0 your values are. Say Excel is giving you ~ -1E-8 (call it XLEPS, rounds to -0.00 to 2SD), and the other source is giving you 1E-10 (call it OTEPS, rounding to 0.00) then CRITERION=RELATIVE will evaluate abs(XLEPS-OTEPS)/OTEPS against your CRITERION i.e. abs(-1E-8 - 1E-10)/abs(1E-10) Vs. 1E-3 and find abs(XLEPS-OTEPS)/OTEPS (1.01E2) is STILL way bigger than your CRITERION value (1E-3). The CRITERION=RELATIVE parameter setting is much more useful when the reference value is not close to your operating environment machine epsilon (which SAS documentation often abbreviates as EPS).

  • RELEVANCY SCORE 2.71

    DB:2.71:Dax: Sum Unique Values Across Columns xc



    Hello,

    How do I sum unique values of Yes across my columns in DAX?

    It should look like this with [Total Yes] being my calculated column:

    Name

    January

    February

    March

    Total Yes

    Bob

    Yes

    Yes

    No

    2

    Jim

    No

    Yes

    No

    1

    Mark

    No

    No

    Yes

    1

    Thanks!

    ~UG

  • RELEVANCY SCORE 2.71

    DB:2.71:Compare Two Ranges And List The Differences dc


    Hi,
    I have 2 ranges (columns) of data and I would like to compare and list the differences using just formulas:
    Col A Col B
    A A
    B C
    BC
    DE
    F F
    I want to compare and list all the unique values in Col A and append the unique but new values in Col B. So the result would be:
    A
    B
    D
    F
    C
    E
    Please advise how would I accomplish this using just formulas in Excel. Thanks!

    DB:2.71:Compare Two Ranges And List The Differences dc

    Hi,
    Using formulars only would be too complicated. If VBA is allowed you can make up something like

    the following:
    (in a codemodule of your worksheet)
    Option Explicit

    Const strColumn1 As String = A
    Const strColumn2 As String = B
    Const strColumn3 As String = I

    Sub UniqueValues()

    Dim rng As Range
    Dim lngR1 As Long
    Dim lngR2 As Long

    Set rng = ActiveCell

    lngR1 = Me.Range(strColumn1 1).End(xlDown).Row
    Me.Range(strColumn1 1: strColumn1 lngR1).Copy
    Me.Range(strColumn3 1).PasteSpecial xlPasteAll

    lngR2 = Me.Range(strColumn2 1).End(xlDown).Row
    Me.Range(strColumn2 1: strColumn2 lngR2).Copy
    Me.Range(strColumn3 lngR1 1).PasteSpecial xlPasteAll

    Me.Range(strColumn3 1: strColumn3 lngR1 lngR2).RemoveDuplicates Columns:=1, Header:=xlNo
    rng.Activate

    End Sub

    To download a test sample click
    here

    Sergiy Vakshul

  • RELEVANCY SCORE 2.71

    DB:2.71:Lookup And Update Target: Issue With Source Records aa



    Hi,

    I have this task of updating a column in a fact table. Mapping loads the newly added column correctly. But on the history update, I'm desiging a patch which will lookup for primary key columns from the target and compare with these values against source. For all matching records, the update is fine. But for non matching records, the source is pulling nulls. I want these null values to be updated with any of the not null values for that set of primary key tied up with another aggregate column. For example, each cateorgy has products identified by unique product_id (which'd be the primary key). For product_ids not present in the source, say the order_number will be null in the target. But I want one uniques order_number for same category with unique products. How do I achieve this?

    DB:2.71:Lookup And Update Target: Issue With Source Records aa


    Hi,

    I have this task of updating a column in a fact table. Mapping loads the newly added column correctly. But on the history update, I'm desiging a patch which will lookup for primary key columns from the target and compare with these values against source. For all matching records, the update is fine. But for non matching records, the source is pulling nulls. I want these null values to be updated with any of the not null values for that set of primary key tied up with another aggregate column. For example, each cateorgy has products identified by unique product_id (which'd be the primary key). For product_ids not present in the source, say the order_number will be null in the target. But I want one uniques order_number for same category with unique products. How do I achieve this?

  • RELEVANCY SCORE 2.70

    DB:2.70:Unique And Non Unique Indexing ax


    Hi, Can any body tell me how to find whether a cluster or non-cluster index is created in a table and on which columns. whether the indexing is unique or non-unique. Is there any query to find this out? In which system tables can i find this information? Thanks, Jude

    DB:2.70:Unique And Non Unique Indexing ax

    This will also work.Reports the information about the indexes on a table or viewSP_HELPINDEX TableNameThanksRamesh. M

  • RELEVANCY SCORE 2.69

    DB:2.69:Create Values For Analogous Periods In The Same Table With Ds 13



    Hi,

    in the course of a pilot we want to create additional columns with data for analogous periods.

    We have values for a month and need to create new columns for values for last month and last year in order to compare.

    Attached youu2019ll find a few sample rows resembling the basic concept of what we have

    RegionCountryYearMonthValue

    EuropeSpain20091285

    EuropeSpain20092129

    EuropeSpain20093153

    EuropeSpain20081288

    EuropeSpain20082284

    EuropeSpain20083125

    EuropeFrance20091135

    EuropeFrance20092125

    EuropeFrance20093284

    EuropeFrance20081208

    EuropeFrance20082221

    EuropeFrance20083220

    In this case, new columns would be

    LastMonth (125 for Spain month 1 year 2009, since this is the value for month 3 year 2008)

    LastYear (288 for Spain month 1 year 2009, since this is the value for month 1 year 2008).

    Can you help on what function to use in the DS query transformation?

    thanks everybody, and enjoy!

    DB:2.69:Create Values For Analogous Periods In The Same Table With Ds 13


    Hi David,

    Probably the easiest way of resolving this would be to add extra columns which show the projected date each row should be shown on e.g.

    RegionCountryYearMonthValueLM_YearLM_MonthLY_YearLY_Month

    EuropeFrance200812082008220091

    EuropeFrance200822212008320092

    EuropeFrance200832202008420093

    EuropeFrance200911352009220101

    EuropeFrance200921252009320102

    EuropeFrance200932842009420103

    This would be written to a staging table then used as a source with the original source file to produce the target table. I've illustrated an example of how this could be achieved below:

    Create two dataflows; DF 1 and 2.

    In DF 1 read source, add additional columns to show the date (year, month, etc) this should be projected as. This would need to be done for both the last month and last year. Load staging table.

    In DF 2 Read the source and outer join to the new staging table twice. Use the region, Country, Year, Month, to join and use the new additional projected date columns. Ensure the last month date fields are used on one staging table and last year date fields are used on the next staging table. The value from the two staging tables is then used in the last month and last year value.

    Load into target table.

    To calculate the projected date columns you would probably want to format the source fields into a date then add the required number of months. e.g. to_date('01' || month || year, 'DDMMYYYY') + 12

    Obviously this would need the stage table to be truncated before each load and you would need to assess your load strategy. When doing these calculated fields if you have any retrospective updates on the source then these would then need recalculating, therefore this assumes it's a complete reload each time.

    Edited by: Richard Saltern on Jul 16, 2010 2:01 PM

    Edited by: Richard Saltern on Jul 19, 2010 10:10 AM

  • RELEVANCY SCORE 2.69

    DB:2.69:Using Vba In Excel To Find Unique Values In A Single Row kj


    I am looking for the simplest way to locate the unique values within one row in an Excel spreadsheet. I have found many ways to do this with columns, but not very many with rows. I did find the code belowfor a column which is very simple
    and was wondering how it could be converted to be used for a row instead of a column.
    Public Sub Test()
    ActiveSheet.Range(A2:A65536).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range(B2), Unique:=True
    End Sub

    Thanks....

    DB:2.69:Using Vba In Excel To Find Unique Values In A Single Row kj

    You can chack and modyfi my solutions

    Unikaty dla zakresu lub stringa
    regards.Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

  • RELEVANCY SCORE 2.69

    DB:2.69:Re: Need Plsql Code To Find Out The Column/Combination Of Columns From A Given Table Which Will Be Unique Values kz


    Hello,you mean a query like the following:Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionSELECT column_1 ,column_2FROM my_tableGROUP BY column_1 ,column_2HAVING COUNT(*) = 1 And you want to build the select dynamically?How do you want to return the output?REF Cursor, dbms_output, ...?RegardsMarcus

    DB:2.69:Re: Need Plsql Code To Find Out The Column/Combination Of Columns From A Given Table Which Will Be Unique Values kz

    If you are lucky and have some tables with single columns that are unique you can use the below query if you have fresh stats computed on 100% of the data. This may get you 80% of what you are looking for.
    SELECT col.table_name, col.column_name
    FROM dba_tab_columns col, dba_tables tab
    WHERE col.owner = tab.owner
    AND col.table_name = tab.table_name
    AND col.num_distinct = tab.num_rows
    AND col.num_nulls = 0
    AND tab.owner NOT IN
    ( 'SYS',
    'SYSTEM',
    'MDSYS',
    'DBSNMP',
    'WMSYS',
    'XDB',
    'APPQOSSYS',
    'OPSG',
    'ORDDATA',
    'ORDSYS',
    'OUTLN',
    'CTXSYS',
    'OE',
    'HR',
    'TOAD' )
    ORDER BY tab.table_name, col.column_name;

  • RELEVANCY SCORE 2.69

    DB:2.69:Comparing Substr Values Of Two Columns 8s


    Say you have a table with 3 columns:
    *Unique random-generated id,
    *First name
    *Mailing name

    I need to find all the rows where the mailing name does not contain the value of the first name column.

    The "like" operator (only used with where clause) doesn't seem to work because what you want to do is:
    select * from table
    where [first_name is *not* found in *part of* (it will never be exact match) the mailing_name]

    What's the correct where clause?

    Any help is appreciated. Thanks

    DB:2.69:Comparing Substr Values Of Two Columns 8s

    How about
    select * from table1 where col3 not like '%'#0124; #0124;col2#0124; #0124;'%'

    Of course you can switch to use the minus operator instead of not like if performance becomes an issue.

  • RELEVANCY SCORE 2.68

    DB:2.68:Compare And Count Two Worksheets ap


    Hello,

    I have in one sheet a productsheet with names,prices and barcodes, in the other sheet i have a list which i made with a scanner and only barcodes(in columns). Is it possible to compare the barcodes and count the unique barcodes and add a column to the first sheet with the counted result?

    DB:2.68:Compare And Count Two Worksheets ap

    Sorry (Jerrold and Yvan) for my "dummies" numbers talk/question (and perhaps it also because of my translation from dutch to english ). But thank you, this it exactly what i wanted Yvan many thanks from Holland for your fast and perfect answer. Mercy beaucoup pour votre temps

  • RELEVANCY SCORE 2.68

    DB:2.68:Conditional Formatting Based On Unique Value 3p


    Simplifying my situation, I have 3 columns, where values in column C are the concatenation of the values in columns A and B. I need the values in column C to be unique, and, in any given row, I want to conditionally format columns A and B if the value
    in column C is not unique. (Ideally, column C will be hidden.) How can I do this. (Version 2010)

    DB:2.68:Conditional Formatting Based On Unique Value 3p

    Unfortunately, I have no idea, since I didn't notice when it stopped working. I was working on several other things. I know it's silly to expect someone who can't see what I'm doing to figure this out. Conditional formatting is really driving me crazy. Can
    you point me to a good reference on the subject? I've looked and looked and haven't found a really good one.
    Thanks.

  • RELEVANCY SCORE 2.68

    DB:2.68:Re: Constraint Based On Existing Values For A Column In The Table 8m


    A unique constraint is a single column or combination of columns that uniquely identify a rows. That means that a row should no have identical rows.

    CREATE TABLE [Table Name]
    ([columns datatype],
    ...
    CONSTRAINT [constraint name] UNIQUE ([colums],...));or if table has been created
    ALTER TABLE [Table Name]
    add CONSTRAINT [constraint name] UNIQUE ([colums],...);

    DB:2.68:Re: Constraint Based On Existing Values For A Column In The Table 8m

    Yeah..
    I checked it as (decode(upper(primary_acct),'Y',upper(primary_acct))

  • RELEVANCY SCORE 2.68

    DB:2.68:Count Unique Values Two Columns Of Data pc


    I need to count the X's in column B only if the values in Column A are unique.
    The example below would resultin a count of 2 (one X for 5262, and one X for 6520)
    I triedseveral formulas (sumproduct, countifs) but can't get the correct answer.

    5262
    X

    6520

    6520
    X

    6520
    X

    6520
    X

    6520
    X

  • RELEVANCY SCORE 2.68

    DB:2.68:Create A Single Column Of Unique Values From Multiple Columns 88


    I have 2 columns of numeric values in columns I and J with headers, LINER X AND LINER Y, respectfully. I would like to create a list of unique values in Column L which has an existing Header. It would be nice to do it with VBA as I would be using it
    on multiple files with varying rows.

    DB:2.68:Create A Single Column Of Unique Values From Multiple Columns 88


    ... I should have probably noted I have Excel 2003.

    Sorry, my fault for not double-checking the version you submitted with the original question.

  • RELEVANCY SCORE 2.67

    DB:2.67:Unique Values For Combination Of Fields In Infopath 2010 az


    Hi,

    We have a list with the following columns - product type , product name. The product type is a lookup column. The products list with the product type and product name columns should save unique values for Product Type and Product Name combination.

    How can we check and prevent the duplicate entry into the sharepoint list using infopath 2010.
    Thanks in advance
    Veena RVeena Raju

    DB:2.67:Unique Values For Combination Of Fields In Infopath 2010 az

    A simple data connection back to the same list and some validation rules that query the data connection to see if any occurrence of that list has Product Type (in the list) equal to Product Type (in the form) where Product Name (in the list) is equal to
    Product Name (in the form). Basically, when both items are entered into the form, fire off a rule that sets the Product Type query parameter to retrieve anything with the same Product Type and then filters on the remaining items to see if Product Name
    matches for any of them. If so, disable the submit button and show an error message in a hidden section that tells the user the item already exists.SharePoint Architect || Microsoft MVP ||
    My Blog
    Planet Technologies ||
    SharePoint Task Force

  • RELEVANCY SCORE 2.67

    DB:2.67:How To Check Row Against Columns Data? 8m


    Hello,
    I am trying to find a solution to compare columns and values stored in another table with actual table column- values

    I have 2 tables

    Table1 with following columns and values

    ID|Col1|Col2| Col3 |Col4 |Col5| Col6| Col7
    --------------------------------------
    1 01 a1 c1 02 03 ef a3

    Table 2 with following columns and values

    ID |Fldname| FldValue | read | Write | delete
    -------------------------------------------
    1 Col1 01 1 0 0
    2 Col1 02 1 1 0
    3 Col3 c1 1 1 1
    4 Col3 b2 1 0 0
    5 Col5 03 1 1 0

    I like to get the following result from these 2 tables

    Fldvalue | Read | write | delete
    ----------------------------------------
    01 1 0 0
    c1 1 1 1
    03 1 1 0

    Is there a way to do all these with TSQL query inside a sp or function

    Below are the scripts to create table and inserting values.

    ----Create Table1

    CREATE TABLE [dbo].[Table1](
    [id] [int] NULL,
    [col1] [char](10) NULL,
    [col2] [char](10) NULL,
    [col3] [char](10) NULL,
    [col4] [char](10) NULL,
    [col5] [char](10) NULL,
    [col6] [char](10) NULL,
    [col7] [char](10) NULL
    ) ON [PRIMARY]

    ------Insert Values
    INSERT INTO [Table1]
    ([id]
    ,[col1]
    ,[col2]
    ,[col3]
    ,[col4]
    ,[col5]
    ,[col6]
    ,[col7])
    VALUES
    (1
    ,'01'
    ,'a1'
    ,'c1'
    ,'02'
    ,'03'
    ,'ef'
    ,'a3')
    GO

    ----Create Table2

    CREATE TABLE [dbo].[Table2](
    [id] [int] NULL,
    [fldname] [char](10) NULL,
    [fldvalue] [char](10) NULL,
    [read] [int] NULL,
    [write] [int] NULL,
    [delete] [int] NULL
    ) ON [PRIMARY]

    -----Inserting Values
    INSERT INTO [Table2]
    ([id]
    ,[fldname]
    ,[fldvalue]
    ,[read]
    ,[write]
    ,[delete])
    VALUES
    (1,'col1','01',1,0,0)
    GO
    INSERT INTO [Table2]
    ([id]
    ,[fldname]
    ,[fldvalue]
    ,[read]
    ,[write]
    ,[delete])
    VALUES
    (1,'col1','02',1,1,0)
    GO
    INSERT INTO [Table2]
    ([id]
    ,[fldname]
    ,[fldvalue]
    ,[read]
    ,[write]
    ,[delete])
    VALUES
    (1,'col3','c1',1,1,1)
    GO
    INSERT INTO [Table2]
    ([id]
    ,[fldname]
    ,[fldvalue]
    ,[read]
    ,[write]
    ,[delete])
    VALUES
    (1,'col3','b2',1,0,0)
    GO
    INSERT INTO [Table2]
    ([id]
    ,[fldname]
    ,[fldvalue]
    ,[read]
    ,[write]
    ,[delete])
    VALUES
    (1,'col5','03',1,1,0)
    GO

    Thanks in advance
    Kris

    DB:2.67:How To Check Row Against Columns Data? 8m


    Hi Naom,
    Perfect, this is what I wanted. I changed little to make it run...
    Thanks a lot
    Kris
    --------------------------------------------------------------------------------------

    declare

    @cols nvarchar(max),
    @SQL nvarchar(max),
    @TableName varchar(10)
    ='Table1'

    select

    @Cols =
    stuff((select
    ', '

    quotename(Column_Name)
    from
    INFORMATION_SCHEMA.COLUMNS

    where

    Table_Name = @TableName
    and Column_Name
    not
    like
    'ID%'
    ORDER
    BY Ordinal_Position

    for

    XML
    path('')),1,2,'')

    set

    @SQL =
    ';with cte as (select * from '

    quotename(@TableName)

    ' UNPIVOT (FldValue for FldName IN ('
    @Cols

    ')) unpvt)

    select T.* from Table2 t INNER JOIN
    cte C on T.FldName = C.FldName and T.FldValue = C.FldValue'

    execute

    (@SQL)

  • RELEVANCY SCORE 2.67

    DB:2.67:Find Records That Occur Once Different From Finding Duplicates af


    I have a worksheet that is a combination of two similar worksheets. The combined file contains records that may occur only once or may occur twice. I would like to find the records that only occur once. The records that occur twice are not needed for my
    search.

    The criteria for this unique status is when the combination of values in columns A,C,D, and E do not match the same combination in any other row. The value in a cell could be repeated in the column (e.g. there can be many Mary's), but the combination of the
    four values makes the record unique. Three of the columns are text. The fourth is a short date.

    The worksheet does contain other columns, but these are not necessary to the search. They do however need to be copied over to a new worksheet/file, if the suggested answer copies the unique records to a new worksheet or file.

    I've tried some conditional formatting and some data tools, but I am not having any luck. I keep getting different results. Any help would be very much appreciated!

    Thank you!

    McLee

    DB:2.67:Find Records That Occur Once Different From Finding Duplicates af

    Hi, Gary,

    I inserted a pivot table and used X (from your example) as the row labels and used a count of W as the value. That allowed me to find the ones that only occurred once. Thank you so much for providing that middle step!

    Thanks again!
    Lee

  • RELEVANCY SCORE 2.67

    DB:2.67:Finding Modified Columns Of The Modified Rows 7z


    Hi,
    I have various columns in a datatable bound to different columns of a datagrid. I am trying to get the changes done in the rows of datatable though datagrid and not only changes but also the values before the changes were being made.
    //dt is the orignial datatable bound to the datagrid
    DataTable dtChangedTable;dtChangedTable = dt.GetChanges(DataRowState.Modified);
    In dtChangedTable I get all the rows that were being modified but I also want to find out which columns were modified. To do that I want to compare the changed rows with their original version, that way I will be able to find out which columns were changed. How can I do this ???
    Thanks,

    DB:2.67:Finding Modified Columns Of The Modified Rows 7z

    I found it myself, I used DataRowVersion indexer.
     
    dtChangedTable.Rows[0][myColumnName,DataRowVersion.Original].ToString();
     
     

  • RELEVANCY SCORE 2.67

    DB:2.67:Compare And Combine Csv Files 7d


    I have a situation where I have to compare 2 csv files and export the csv based on the result. What I would also like to do, which I have not been able to find after searching for so long, is to export all the values from the 2nd column of the 2nd CSV file
    after comparision.
    File1.csv has these values
    ID, Department, Title
    ID1, SomeDepartment, SomeTitle
    ID2, SomeDepartment, SomeTitle
    ID3, SomeDepartment, SomeTitle
    ID4, SomeDepartment, SomeTitle
    The File2.csv has only two columns withthese values
    ID, Group
    ID1, SomeGroup
    ID2, SomeGroup
    ID2, SomeGroup
    ID3, SomeGroup
    ID3, SomeGroup
    ID4, SomeGroup
    I need to compare the 2 csv files and then export the result into a csv but include both columns of File2.csv. Here is what I have so farbut my exported csv only shows up with one column of ID and then a column of SideIndicator. After comparing, I
    need both columns from File2.csv
    $csv1 = import-csv C:\File1.csv
    $csv2 = import-csv C:\File2.csv
    $result1 = compare-object $csv1 $csv2 -Property ID -includeEqual
    $result1 | where {$_.SideIndicator -eq =} | export-csv C:\Result.csv -NoTypeInformation
    Any help will be appreciated

    DB:2.67:Compare And Combine Csv Files 7d

    Sorry for my late reponse. Thanks for your tips/idea. After further review, and doing MORE homework, I realized thatmy column headers were mismatched in the CSV files. The column heading was not ID but 'Column1' in one of the CSV files so I had to
    match the column heading and thenCompare-Object worked beautifully for me. Not bad for my FIRST EVER PS Script!!!! ..By the way that was my Chemistry class in which I fell asleep.
    Here is what worked for me.
    $csv1 = import-csv C:\File1.csv | Select @{Name=ID;Expression={$_.Column1}}
    $csv2 = import-csv C:\File2.csv
    $result1 = compare-object $csv1 $csv2 -Property ID -PassThru
    $result1 | where {$_.SideIndicator -eq =} | Select ID, Group | export-csv C:\Result.csv -NoTypeInformation

  • RELEVANCY SCORE 2.67

    DB:2.67:Which Index I Should Create Btree Or Bitmap Index? ds


    I have table with columns c1,c2,c3

    I want to create index on column c1

    which index I should create Btree or Bitmap index
    the column contain 50% unique values and 50% duplicate values

    If Btree why?
    If Bitmap Why?

    I know that
    Btree is used when there more unique values (high cardinality)
    Bitmap is used when there less unique values (low cardinality)

    DB:2.67:Which Index I Should Create Btree Or Bitmap Index? ds

    Hi ,

    Can you pls expalin how index ( both btree and bitmap) work in the background with example

  • RELEVANCY SCORE 2.67

    DB:2.67:Remove Duplicates Function Doesnt Work fp


    I'm trying to compare the data in 3 columns and only want to keep the cells with unique values. I understand you can do this in three ways: conditional formatting, filter for unique values or use the "remove duplicates" function. I used the conditional
    formatting first to test it before I started deleting, and that works - it shades the cells that are unique. However, when I try to filter for unique values or "remove duplicates" I get a message that there are no unique values. Please help! what am I doing
    wrong? I'm using some simple numbers as a test and it's still not working!
    Since the shading worked, I wanted to somehow sort by shading or incorporate the shading in an "if" statement, then sort by that result and then delete but I don't know if this is possible. Does anyone know?

    DB:2.67:Remove Duplicates Function Doesnt Work fp

    Just in case what you really want is a formula that counts the number of unique items in a range...
    Using the sample data that Bill posted, in A1:C4
    abc
    acb
    dba
    abc
    This formula returns the count of unique items:
    D1: =SUMPRODUCT((A1:C4"")/COUNTIF(A1:C4,A1:C4""))
    For the above data, the formula returns: 4...(representing a, b, c, and d)
    Does that help?

    Ron Coderre
    Microsoft MVP - Excel (2006 - 2010)

    P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

  • RELEVANCY SCORE 2.67

    DB:2.67:Extracting Unique Records From Two Different Tables Record 7x


    Hello,

    In the following code of two different tables www.testing.com exists in both tables. I want to compare two different columns of the two different tables to get unique records.

    SQL select unique(videoLinks) from saVideos where sa_id=21;

    VIDEOLINKS
    -----------------------------------------------------------------------
    www.testing.com

    SQL ed
    Wrote file afiedt.buf

    1* select unique(picLinks) from saImages where sa_id=21
    SQL /

    PICLINKS
    -----------------------------------------------------------------------
    test
    test14
    www.hello.com
    www.testing.comThanks best regards

    DB:2.67:Extracting Unique Records From Two Different Tables Record 7x

    Unfortunatly you didn't mention the expected output. I guess it would be the one line
    "www.testing.com"

    in that case simply join the two tables.

    select *
    from saVideos v
    join saImages i on i.sa_id = v.sa_id and i.picLinks = v.videoLinks
    where v.sa_id=21;If needed then you could change the select list to retrieve only distinct values.
    select unique v.sa_id, v.videolinks
    from saVideos v
    join saImages i on i.sa_id = v.sa_id and i.picLinks = v.videoLinks
    where v.sa_id=21;I usually avoid distinct/unique whereever possible. This requires the database to do a sort and makes the query slow.

    Edited by: Sven W. on Feb 10, 2011 1:55 PM

  • RELEVANCY SCORE 2.67

    DB:2.67:Filtering Duplicate Values While Inserting 9f


    Hi ,
    I have to insert call data records from a table (MAIN TABLE) to a TARGET TABLE and I have to filter duplicate values. There is a second table containing previously read records(TAG TABLE). Tag table contains 8 columns(unique columns for the record).

    MAIN TABLE contains 100 columns including the 8 columns in TAG table. 8 of the unique columns are nullable. Both MAIN and TAG tables are range partitioned on call date. Also, both are hash paartitioned on the 8 columns.

    Tag table contains 12 billion rows on 80 date partitions. Number of records in the MAIN TABLE vary between 5 million to 20 million.

    Both tables have up to date statistics.

    My query is basically like

    insert /*+ append*/ into TARGET_TABLE
    select /*+ hash_aj */ * from MAIN_TABLE
    where (col_1 ,col_2 , ... col_8) not in
    (
    select col_1 ,col_2 , ... col_8 from TAG_TABLE
    )

    How should I filter duplicates from going to main table ? This query takes more than 2 hours.

    1-) Design of the tables is not fixed, so is this a good design ?
    2-) Should I loop on call date to find duplicates or should I join the 5M to 12B at once ?
    3-) Any thing else you'd suggest for such a problem ?

    Thanks in advance...

    DB:2.67:Filtering Duplicate Values While Inserting 9f

    user808700 wrote:
    1-) Design of the tables is not fixed, so is this a good design ?Do you honestly expect us to be able to evaluate that based on what you've posted? You've omitted about 100% of the details required to be able to assess this.

    user808700 wrote:
    2-) Should I loop on call date to find duplicates or should I join the 5M to 12B at once ?I don't really know what you mean here, but generally speaking "looping" (which implies PLSQL to me) would be a horrendous idea for the volumes of data you are dealing with.

    user808700 wrote:
    3-) Any thing else you'd suggest for such a problem ?Yes, but you'd have to spend some time outlining your business problem, the current table design, the volumes involved, your version of Oracle, etc....

  • RELEVANCY SCORE 2.67

    DB:2.67:Compare 2 Fields And Find Difference In Text Values mz


    I keep history of changes in text field - move previous value in history table before apply new changes. Is it any way to compare old and new values and highlight changes - something like Unix DIFF or WinDiff to compare text files string by string ?

    DB:2.67:Compare 2 Fields And Find Difference In Text Values mz

    That's how you can do it:

    http://en.wikipedia.org/wiki/Longest_common_subsequence_problem

    I even have a PL/SQL code for it.
    It's not very fast, but if your text is not very big it works fine.

    Lev

  • RELEVANCY SCORE 2.66

    DB:2.66:Search In Multiple Tabs For Unique Combination Of 2 Cells p9


    Hi,

    I would like to write a code that returnsvalue in the cell that is a few colums next to a unique combination of 2 cells.
    For example you have 3 columns in tab1
    NUMBER |NAME| PRICE

    and you have multiple columns in tabs 1 to 50 with
    ORDERNR. | NUMBER | NAME | QUANTITY | PRICE

    In tab 1, number and name are filled in but we need to find the price in the tabs.
    Nor number, nor name are unique, only the combination is.
    So I need to do a search in the tabs to get the price, it's like a vertical search but with two values, multiple tabs.

    DB:2.66:Search In Multiple Tabs For Unique Combination Of 2 Cells p9

    Hi Piri10,
    Since the orignal issue was resolved, I marked the helpful replies to close this thread. If you have other questions, please feel free to reopen a new thread. It will be helpful for other communities who have the same issue to find the answer quickly.
    Best regards
    FeiWe are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

    Click
    HERE to participate the survey.

  • RELEVANCY SCORE 2.66

    DB:2.66:Count Of Unique Values In 7 Columns j3


    Hey there
    I have a Lookup table with 8 columns
    ID = unique ID
    U = Sunday,
    M = Monday
    T = Tues
    W = Weds
    R = Thur
    F = Fri
    S = Sat

    Each of the day columns can have a value between 1 and 1150, looks like this

    ID U M T W R F S
    1 15 15 16 15 17 345 17

    What I am trying to find is a count of the deviation of each row or how many of the days have different values

    So for the above example

    ID U M T W R F S COUNT
    1 15 15 16 15 17 345 17 4

    As there are 4 unique values in the week..
    15,16,17,345

    Any ideas on how that could be done in oracle? Stumped!

  • RELEVANCY SCORE 2.66

    DB:2.66:Sum Error In Charts sx



    hi,

    I have three columns..

    ABAmount1000255032100082150078901300895425130060

    I need to sum up the Amount when Column A = Column B. eg: First 2 rows, 1000 is in both columns but has different adjacent values, so i have to sum up 50 + 82 and similarly Row 4 and 5, since 1300 is in both columns need to sum up, 60 + 54. whereas row 3, is a single unique line item and that remains the same. Plz help me find the right expression.

    DB:2.66:Sum Error In Charts sx


    Hi,

    Can you do an applymap function in the script level between A B to get the amount correspondingly ,

    and call this calculated amount in the expression directly

    Regards,

    Kiruthiga

  • RELEVANCY SCORE 2.66

    DB:2.66:Unwanted Suppression Of Duplicate Rows p3


    Hi,

    I am on 10.1.3.4

    I have a business area where all of the underlying folders have no aggregation applied to any of the columns.

    I have some cells that are calculated, but nothing more complex than A - B or substring(A,1,6)....

    When I pull my query back I find that it suppresses duplicate values - unless I pull back enough columns to ensure that the permutation of all columns is unique.

    I can get around this by doing this and then hiding the unwanted columns.

    Changing all columns to aggregation = none on the function bar does not work.

    Any idea why I am getting this behaviour, what fix to apply??

    thanks for your thoughts,

    Robert.

    Many thanks for your input,

    Robert.

    DB:2.66:Unwanted Suppression Of Duplicate Rows p3

    Many thanks for this, the ability to remove distinct from DB would do it - I had not know of this feature - most useful - thank you.

    regards,

    Robert.

  • RELEVANCY SCORE 2.66

    DB:2.66:Unique Columns z8


    Hello All,

    Is there a way by which I can make a column unique by not making it unique on the database side.Suppose I want that my employee-id column be unique on the forms side but on the database side its definition is like any other column means a non-unique column.To be precise I want to avoid insertion of duplicate values in a particular column on the forms side itself.

    Thanks and Regards,

    Kapil Uppal

    DB:2.66:Unique Columns z8

    Andrew ,

    Point Taken...
    Even I am baffled at such a business requirement.But it is one of those cases now where I'll inform the Leads about this concern.

    Thanks and Regards,

    Kapil Uppal

  • RELEVANCY SCORE 2.66

    DB:2.66:How Do I Compare Two Columns Of Data In Numbers To Find The Unique Results? p1


    Hello all,I'm looking for a way to compare the data from two columns so I can find the unique results and display that data in a third column. To be specific, here's what I'm doing. Column A is a list of email addresses for people I have already written. Column B is a list of email addresses for people I would like to contact. I am not sure if there are email address from Column A in Column B, but there may be and if there are, I must find out so that I don't send a second email to these recipients. How can I have Numbers look at both columns and tell me which email addresses in Column B are not in Column A?Thanks for the help!!!

    DB:2.66:How Do I Compare Two Columns Of Data In Numbers To Find The Unique Results? p1

    Thanks everyone for your insight and help. These ideas were all really useful.

  • RELEVANCY SCORE 2.66

    DB:2.66:Compare Values ps


    I have data for the following columns. A2 - Client Name - B2 - Placement Type - C2 - Goal (as ofApril 30). I have other columns with the same header, yet the end date would be November 30th.
    Thus, what I want to know is where there any changes from the originalApril 30thsheet in one of the 3 columns?
    help!!

    DB:2.66:Compare Values ps

    Ok. Thanks!!!Where would the results show or how would the differences show?

  • RELEVANCY SCORE 2.66

    DB:2.66:Compare Columns In Excel mk


    hi

    Can anyone help on a way we can compare two columns in excel,tried Vlookup but doesnt seem to bring the correct values

    please assist asap

    DB:2.66:Compare Columns In Excel mk

    hi

    Can anyone help on a way we can compare two columns in excel,tried Vlookup but doesnt seem to bring the correct values

    please assist asap

  • RELEVANCY SCORE 2.66

    DB:2.66:Is There A Dax Function That Will Search Within A Field? f3


    We are looking for a solution to the problem of how we can look up our users based on their capabilities. The current solution has a column in the table for each capability (Capabillity_1, Capability_2 etc) and a disconnected table that contains a unique
    list of the possible capabilities.
    At the moment I have a DAX function working that uses a slicer to look up unique capabilities across values stored in 5 columns.
    IF(HASONEVALUE(Data[Capability_1])
    HASONEVALUE(Data[Capability_2])
    HASONEVALUE(Data[Capability_3])
    HASONEVALUE(Data[Capability_4])
    HASONEVALUE(Data[Capability_5]),
    IF(
    CONTAINS(
    VALUES(Choices[Capabilities]),
    Choices[Capabilities],
    VALUES(Data[Capability_1])
    ) ||
    CONTAINS(
    VALUES(Choices[Capabilities]),
    Choices[Capabilities],
    VALUES(Data[Capability_2])
    ) ||
    CONTAINS(
    VALUES(Choices[Capabilities]),
    Choices[Capabilities],
    VALUES(Data[Capability_3])
    ) ||
    CONTAINS(
    VALUES(Choices[Capabilities]),
    Choices[Capabilities],
    VALUES(Data[Capability_4])
    ) ||
    CONTAINS(
    VALUES(Choices[Capabilities]),
    Choices[Capabilities],
    VALUES(Data[Capability_5])
    ),
    1,
    BLANK()
    ),
    BLANK()
    )
    The obvious disadvantage to this is that as people add in more capabilities we have to add more columns, and some joker has just added in over 100 capabilities!
    What I would like is to find a way of looking up the unique values in column that has a users capabilities comma separated, is this possible?
    In the diagram above the red line indicates what I have now (and is working) and the black line indicates what I would like.
    In the red section Column 1 is my 'unique list' table and capability_1 to _5 are the lists of values.
    In the black section Column 1 is my 'unique list' table and capability_1 is the comma separated column of capabilities
    Please feel free to ask if anything isn't clear, or even the solution needs to be addressed further upstream - at the database level.
    Thanks
    Paul

    DB:2.66:Is There A Dax Function That Will Search Within A Field? f3

    Thanks for your posting - that seems to work OK. I also stumbled across another solution:
    =If ( Hasonevalue(Data[Capability_1] ),
    Calculate (
    LASTNONBLANK (Choices[Capabilities], 1 ),
    Filter ( Choices, SEARCH(Choices[Capabilities],Values(Data[Capability_1]),1,0) 0 )
    )
    )
    I am going to test them both and see which one is most performant.
    Thanks
    Paul