• RELEVANCY SCORE 3.62

    DB:3.62:How Do I Count The Unique Text Entries In A Column zm




    I want to count thenumber of unique text entries in a column how do I do that?

    DB:3.62:How Do I Count The Unique Text Entries In A Column zm

    Or, if you will need to do this on different documents, you can copy this script into AppleScript Editor, select the cells in whichever document you are working on, and run. No need to set up the formulas and extra column again.SG--select range of cells, run; provides count of distinct valuestell application "Numbers" to tell the front document to tell active sheet set selectedTable to (the first table whose class of selection range is range) tell selectedTable to tell the selection range set distinctValues to {} repeat with aCell in its cells tell aCell if its value is not in distinctValues then copy its value to the end of distinctValues end tell end repeat return count of distinctValues end tellend tell--end of script

  • RELEVANCY SCORE 3.50

    DB:3.50:Count Unique Cells In A Column 1j




    Is there a way to count the number of unique entries in a column that has duplicated entries?

    DB:3.50:Count Unique Cells In A Column 1j

    A few ways more :-)

    all unique values (text and numbers, no empty cells)

    =SUMPRODUCT(--(MATCH(A2:A10,A2:A10,0)=ROW(A2:A10)-ROW(A2)+1))

    All unique values (text and numbers, empty cells possible) - array formula (CTRL+SHIFT+ENTER)

    =SUM(--(IF(A2:A10="",0,MATCH(A2:A10,A2:A10,0))=ROW(A2:A10)-ROW(A2)+1))

    If you need only unique numbers (text and numbers in range, empty cells possible) - array formula (CTRL+SHIFT+ENTER)

    =SUM(--(IF(ISNUMBER(A2:A10),MATCH(A2:A10,A2:A10,0)=ROW(A2:A10)-ROW(A2)+1)))

    If you need text only (text and numbers in range, empty cells possible) - array formula (CTRL+SHIFT+ENTER)

    =SUM(--(IF(ISTEXT(A2:A10),MATCH(A2:A10,A2:A10,0)=ROW(A2:A10)-ROW(A2)+1)))

    Regards

  • RELEVANCY SCORE 3.43

    DB:3.43:Quoted Identifier (Dash In Column Name) - And Dbms_Xmlsave 31




    I HAD to create a View where the column names had embedded dashes in order to correspond to the XML I am receiving. The view contains the columns:
    V_VEND_H_XML - view name
    BATCH-UNIQUE-ID
    ADD-COUNT
    MODIFY-COUNT
    DELETE-COUNT
    REACTIVATE-COUNT
    TRANSACTION-DATE
    ACTION-TIME

    When I created it and when I reference a column I am supposed to put the column name in double quotes (").

    I am encountering 2 problems when I try to use these column.
    1. I need to do the following functions/procedures and provide the column name:
    DBMS_XMLSave.setKeyColumn(l_ctx,'"BATCH-UNIQUE-ID"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"ADD-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"MODIFY-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"DELETE-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"REACTIVATE-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"TRANSACTION-DATE"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"ACTION-TIME"');
    DBMS_XMLSave.setCommitBatch(l_ctx, 0);
    V_rowcount := DBMS_XMLSave.updateXML(l_ctx, v_clob);
    (I have tried with and without the double quotes - inside the require single quotes)

    In the example above, I get an error which implies that the package code is changing the case to lower case (see "batch-unique-id" below)

    ERROR at line 1:
    ORA-29532: Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: Column '"batch-unique-id"', specified to be a key column, does not not exits in table 'V_IFMIS_VEND_XML_H_XMIT2'.
    ORA-06512: at "SYS.DBMS_XMLSAVE", line 65
    ORA-06512: at line 48

    Does anyone know how I can make this work?

    DB:3.43:Quoted Identifier (Dash In Column Name) - And Dbms_Xmlsave 31

    I HAD to create a View where the column names had embedded dashes in order to correspond to the XML I am receiving. The view contains the columns:
    V_VEND_H_XML - view name
    BATCH-UNIQUE-ID
    ADD-COUNT
    MODIFY-COUNT
    DELETE-COUNT
    REACTIVATE-COUNT
    TRANSACTION-DATE
    ACTION-TIME

    When I created it and when I reference a column I am supposed to put the column name in double quotes (").

    I am encountering 2 problems when I try to use these column.
    1. I need to do the following functions/procedures and provide the column name:
    DBMS_XMLSave.setKeyColumn(l_ctx,'"BATCH-UNIQUE-ID"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"ADD-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"MODIFY-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"DELETE-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"REACTIVATE-COUNT"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"TRANSACTION-DATE"');
    DBMS_XMLSave.setUpdateColumn(l_ctx,'"ACTION-TIME"');
    DBMS_XMLSave.setCommitBatch(l_ctx, 0);
    V_rowcount := DBMS_XMLSave.updateXML(l_ctx, v_clob);
    (I have tried with and without the double quotes - inside the require single quotes)

    In the example above, I get an error which implies that the package code is changing the case to lower case (see "batch-unique-id" below)

    ERROR at line 1:
    ORA-29532: Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: Column '"batch-unique-id"', specified to be a key column, does not not exits in table 'V_IFMIS_VEND_XML_H_XMIT2'.
    ORA-06512: at "SYS.DBMS_XMLSAVE", line 65
    ORA-06512: at line 48

    Does anyone know how I can make this work?

  • RELEVANCY SCORE 3.07

    DB:3.07:Appleworks 6.2.9 Spreadsheet Sorting 38


    I am running Appleworks 6.2.9 on my PPC G4 Laptop. I have a one column list of data of approx 12,000 entries. Each one is not unique, rather they're duplicates in assorted quantities.Is there a sort function where the program can eliminate duplicates so my final list is only one of each entry, but with some sort of column with a frequency count that lists how many times each entry appeared?

  • RELEVANCY SCORE 3.04

    DB:3.04:Unique Key At Column Level Vs Constraint. 1x


    Is there a difference when implementing Unique keys at a column level vs as constraints? Are there any performance ramifications?

    Additionally, is there a way to define unique keys as asc/desc at a column level as we can at a constraint level? Thanks all.

    DB:3.04:Unique Key At Column Level Vs Constraint. 1x

    COLUMN level and CONSTRAINT level are the same thing, as CONSTRAINTs are on the individual COLUMNs.

  • RELEVANCY SCORE 3.00

    DB:3.00:Countif j7


    I'm trying to use countif to count how many times column M has a number (or range of numbers) when there is an entry in column K.

    Column K has only about 25% of the entries in column M. The rest are null or empty cells.

    This is where I am:

    =IF(COUNTIF('APR2013'!K:K,"=0"),COUNTIF('APR2013'!M:M,"=0"))

    And I keep getting all the results (about 500) when I'm looking for around 140.

    Thanks,
    Mark

    DB:3.00:Countif j7

    I think I solved it myself.

    I made another column which I did a formula that showed a number only when there is a value in "K".

    I used that column instead and it came out correct.

    Thanks for your help guys.

  • RELEVANCY SCORE 2.96

    DB:2.96:Sum Of Xs In A Column. The Only Thing Unique Is Some Xs Are White And Some Xs Are Black. j7


    How do I count (sum) cells that contain an x, font black.
    I do not need to countcells that contain an x thathave been conditioned to bewhite.
    Conditioning format - columns have been conditioned so that when there are 2 or more x'sentered in a single column the x's becomes white in colorand you can't see them. I need to hide x's when there are 2 or more x's in a column.
    I need a formula to determine the sum of the remaining x's for that column?

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Count A Distinct Column Value In An Interactive Report s3


    Hi again,

    Just realised that this thread.

    Interactive report unique row count

    Is asking the same question.

    Will watch this also.

    Cheers
    Yog

    DB:2.93:Re: Count A Distinct Column Value In An Interactive Report s3

    Thanks Guys,

    I feel I should jump to my DBA's defence here.

    I totally agree with what you're saying. However, I probably unfairly laid all the blame with the DBA. The app I'm working on is part of a much wider set of applications that all, to a large extent, feed into a central "pot" of views. Changing these views is a big deal and involves all kinds of impact assessments that I don't even want to consider.

    My DBA is involved in this process so it's probably unfair to just call him a DBA...

    And, in case you're wondering, no, I'm not just grovelling to my DBA, he's not read this and would be tickled if he did!

    Anyway, part of the development process is to use the Interactive reports to query ALL of the columns in a given view (and again there are loads of these views). To keep development under control it was agreed early on that changes to reported data should be handled centrally by a seperate development team, hence select * from view. I didn't actually say that it was the DBA that did this by the way.

    Anyway, I'm not here to justify any of the above. Just didn't want to see a collegue hammered in a forum because I didn't explain myself properly.

    Apologies all round!

    Anyway Iulian, if the offer is still open I'd love to find out more. I will email you directly.

    Cheers
    Yog

  • RELEVANCY SCORE 2.92

    DB:2.92:How To Query For Non-Unique Users Base On A Field That Should Be Unique Using Gliderecord ck



    We create users in service-now by syncing with an external DB via LDAP, but get duplicate users generated when a user's name is changed. I'm trying to detect the duplicate entries in sys_user by querying for a mapped field that should be unique. I'm able to do it with the following SQL query:Select "u_unique", COUNT(1) from sys_user GROUP BY "u_unique" HAVING COUNT(1) 1 ORDER BY "u_unique" How do I make the same query using GlideRecord? I would like to write a script the would be run as a scheduled job to clean up the sys_user table, but first I need to be able to make the above query or any query that return the necessary result.Thanks,George

  • RELEVANCY SCORE 2.91

    DB:2.91:Count Unique Values In Column cx


    I am using the following formula to count for unique values in a table column:
    =SUM(IF(FREQUENCY(MATCH(A5:A11,A5:A11,0),MATCH(A5:A11,A5:A11,0))0,1))
    With the current formula, I have to manually change the range each time I add information. I would like to be able to set the range (a5:a100). The current formula will not work if there are blank cells in the range. How can I have count for unique values
    minus the blank cells?
    Thanks!

    DB:2.91:Count Unique Values In Column cx

    I am using the following formula to count for unique values in a table column:
    =SUM(IF(FREQUENCY(MATCH(A5:A11,A5:A11,0),MATCH(A5:A11,A5:A11,0))0,1))
    With the current formula, I have to manually change the range each time I add information. I would like to be able to set the range (a5:a100). The current formula will not work if there are blank cells in the range. How can I have count for unique values
    minus the blank cells?
    Thanks!

    You're working with a relatively small range so in that case I would use this normally entered formula:
    =SUMPRODUCT((A5:A100"")/COUNTIF(A5:A100,A5:A100""))

    And, if the data happens to be numbers it's even easier.
    =SUM(--(FREQUENCY(A5:A100,A5:A100)0))
    --
    Biff
    Microsoft Excel MVP

  • RELEVANCY SCORE 2.88

    DB:2.88:Ssort And Count Unique Values Within A Single Column Containing Multiple Records Of Similar Values 9c


    I wish to sort and count unique values within a single column containing multiple records of similar values. e.g. for a single column containing 2 entries for "bird", 3 entries for "cow" and 9 entries for "fish" I would create two new adacent colums: NC1
    names the discrete records, e.g. NC1R1 is "Bird", NC1R2 is "Cow", NC1R3 is "fish". NC2 counts those unique values, e.g. NC2R1 is "2", NC2R2 is "3" and NC2R3 is "9".Thanks very much. An excerpt from the actual spreadsheet is copied below, which may help.
    I eventually arrived at the values I needed, e.g. 2381 total with5 discrete states, of which 2 are "closed", 2303 are "established", 4 are "fin_wait", 67 are "listen" and 5 are "tim_wait", but very inefficiently. There must be a better way. Thanks for reading.

    Send-Q
    Local
    Foreign
    State
    Unique State Labels

    Total

    2381
    5
    2
    2303
    4
    67
    5
    2381

    0
    *.*
    *.*
    CLOSED
    CLOSED
    CLOSED
    ESTABLISHED
    FIN_WAIT_2
    LISTEN
    TIME_WAIT

    0
    *.*
    *.*
    CLOSED
    ESTABLISHED
    CLOSED
    ESTABLISHED
    FIN_WAIT_2
    LISTEN
    TIME_WAIT

    0
    10.21.1.128.23
    10.21.29.143.1887
    ESTABLISHED
    LISTEN
    ESTABLISHED
    FIN_WAIT_2
    LISTEN
    TIME_WAIT

    0
    *.7
    *.*
    LISTEN
    TIME_WAIT
    ESTABLISHED
    FIN_WAIT_2
    LISTEN
    TIME_WAIT

    0
    *.9
    *.*
    LISTEN
    FIN_WAIT_2
    ESTABLISHED
    LISTEN
    TIME_WAIT

    0
    *.13
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    *.19
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    *.21
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    *.22
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    *.23
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    *.25
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    *.37
    *.*
    LISTEN
    ESTABLISHED
    LISTEN

    0
    10.21.1.128.22
    10.20.184.73.52350
    ESTABLISHED
    ESTABLISHED
    LISTEN

    0
    10.21.1.128.30001
    10.89.1.70.55062
    ESTABLISHED
    ESTABLISHED
    LISTEN

    0
    10.21.1.128.1972
    10.89.25.129.3396
    ESTABLISHED
    ESTABLISHED
    LISTEN

    DB:2.88:Ssort And Count Unique Values Within A Single Column Containing Multiple Records Of Similar Values 9c

    I see your description. But in the worksheet sample you posted I do not see any entry that contains "fish", "Bird", "cow".
    If the pivot table does not work out, then you could post a sanitized copy of your worksheet to Sky Drive or some other sharing site, and post the link here.
    Pivot tables are fairly simple for your purposes.
    If I understand what you want to do, you can Insert/Pivot Table (on a new sheet).

    Then drag the label of the column for which you want to generate a unique list to the Rows area. And drag it again to the Values area (those names might be a bit different in Excel 2010).

  • RELEVANCY SCORE 2.84

    DB:2.84:Countif Year Of Date Is... k9


    I'd like to count all the entries in my table whose date column includes the year 2009. However, the dates in this column are formatted like this: 12/07/09

    How can I count only entries in 2009?

    DB:2.84:Countif Year Of Date Is... k9

    Assuming that the dates are in column A, I would create an auxiliary column whose cells will contain the formula

    =YEAR(A)
    So it will be easy to get the count.

    An other soluce would be to drop the auxiliary column but use the COUNTIFS() function :

    In cell D1 insert the number of the year to test
    D1 : 2009
    in D2 insert : =DATE(D$1,1,1)
    in D3 insert : =DATE(D$1+1,1,1)
    in D4 insert : =COUNTIFS(A,"="D2,A,""D3)

    Yvan KOENIG (VALLAURIS, France) lundi 7 dcembre 2009 21:56:38

  • RELEVANCY SCORE 2.83

    DB:2.83:Exception Not Raised On Duplicate Entries (Constraints Enforced And Columns Unique cj


    My dataset does not throw an exception when duplicate entries are added to a table. The dataset is set to enforce constraints and the columns in question are set to
    unique.
    This happens regardless of whether .BeginLoadData is called.
    //Add to MemberWords
    DataRow dr3 = dataTableMemberWords.NewRow();
    dr3 = dataTableMemberWords.NewRow();
    dr3[Lexicon] = idLexicon;
    dr3[Memberword] = wordToAdd;
    dr3[Family] = headword;
    dataTableMemberWords.Rows.Add(dr3);

    The Memberword column is set to unique, but duplicates do not throw an exception when added.

    DB:2.83:Exception Not Raised On Duplicate Entries (Constraints Enforced And Columns Unique cj

    I don't think there's a bug in the DataSet Designer. I also use VS2010.
    I don't normally use the Designer (I just work with the xml directly in the .xsd), because I don't use (and don't like) the TableAdapter stuff. But, I have a little test project I use for experimenting with stuff that I don't use in my own realapps.
    So, I took a look at one of my test DataSets that does use TableAdapters, and went to the Designer and played with the EnforceConstraints property. The settingof true (or false), is properly carried forward into the code. All I did was set a debugbreakpoint,
    instantiate the new MyDataSet(), then looked at ds.EnforceContraints while in the debugger. It was properly set every time. So, I still think that you had turned it off somewhere and forgot to turn it back on.~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

  • RELEVANCY SCORE 2.83

    DB:2.83:Count Unique Values 8c


    I have a linear range (one column, consecutive cells) set to the variable TheRng.
    I wish to count the number of unique values in that range using VBA.
    All the values are strings. How would I do this?
    Thanks for your time. Otto

    DB:2.83:Count Unique Values 8c

    Thanks Chip. That works for me. Otto

  • RELEVANCY SCORE 2.82

    DB:2.82:Solve The Query k9


    Hi,

    I have one query

    select
    count(unique(case when a.order_dt=sysdate-45 and b.VIDEOS='Y' then a.id end)) one_month_1,
    count(unique(case when a.order_dt=sysdate-105 and b.VIDEOS='Y' then a.id end)) three_month_1,
    count(unique(case when a.order_dt=sysdate-45 and b.VETERAN='Y' then a.id end)) one_month_2,
    count(unique(case when a.order_dt=sysdate-105 and b.VETERAN='Y' then a.id end)) three_month_2,
    count(unique(case when a.order_dt=sysdate-45 and b.CAT='Y' then a.id end)) one_month_3,
    count(unique(case when a.order_dt=sysdate-105 and b.CAT='Y' then a.id end)) three_month_3
    from product a, profile b
    where a.id=b.id;

    above query output is
    One_month_1 three_month_1 One_month_2 three_month_2 One_month_3 three_month_3
    25 38 78 98 29 36

    I want to show output like as

    desc one_month three_month
    Videos 25 38
    Veteran 78 98
    CAT 29 36

    Please help

    DB:2.82:Solve The Query k9

    Personally I would do:

    select case when b.videos = 'Y' then 'Videos'
    when b.veteran = 'Y' then 'Veteran'
    when b.cat = 'Y' then 'Cat' end descr
    count(unique case when a.order_dt = sysdate-45 then a.id end) one_month,
    count(uniquecase when a.order_dt = sysdate-105 then a.id end) three_month
    from product a, profile b
    where a.id = b.id;Seems a lot cleaner to me.

    John

  • RELEVANCY SCORE 2.82

    DB:2.82:Filter For Distinct Field s3



    Hi,

    I have a table A where each record has a unique ID.

    I have created a table B from table A by concatenating various conditions.

    I have now realised some records appear 2 or more times in table B as they belong to 2 or more conditions. How can I filter to only show each unique ID once?

    I realised i can do count (distinct unique ID) in a chart, however this adds an additional column which i dont want.

    Does anyone know of a solution to this problem?

    Thanks,

    DB:2.82:Filter For Distinct Field s3


    Hi ,

    Try this code below

    tableB:

    load
    1,
    2
    resident tableA
    where condition1;

    concatenate
    Load *
    where not Exists(1); //Field1 Name
    load
    1,
    2
    resident tableA
    where condition 2;

    Regards

    //Yusuf

  • RELEVANCY SCORE 2.82

    DB:2.82:Count Unique Values m8


    Hi this is what i have

    Column c Column G
    site cancelled x1
    site cancelled x1
    x2
    x3
    site cancelled x4

    what i want is a formula to count the unique values in column gthat aree site cancelled in column c
    so using the example above the result wouyld be 2 because there is 2 x1 and 1 x4

    DB:2.82:Count Unique Values m8

    Hi,

    You may refer to my solution at the following link -
    http://www.ashishmathur.com/count-uniques-with-conditions/

    Hope this helps.

  • RELEVANCY SCORE 2.80

    DB:2.80:Inventory Tracking And Reconciliation cj



    I have the following Issue when trying to reconcile inventory counts. When I go to :

    INVENTORY Inventory TransactionsInitial Quantities, Inventory Tracking and PostingInventory Tracking Tabclick OK.

    I double click on the "Counted in Whse" to display quantities (from smallest to largest) and I can now view all items with a check mark in the "COUNTED" column.

    The next step is to reconcile the count, you go to:

    INVENTORY Inventory TransactionsInitial Quantities, Inventory Tracking and PostingInventory Posting Tabclick Reconcile. This action should reconcile the count in SAP and in the inventory tracking screen you should see noting checked marked in the "COUNTED" column and no entries in the "Counted in Whse" column.

    Our problem is, when you return the screen Inventory Tracking, you still see the check marks in the "COUNTED" column and a count in the "Counted in Whse" column. Its as if nothing was reconciled and the exact items are still listed as counted and they are awaiting to be reconciled.

    I thought I would post this issue here before I submitted a request to SAP.

    DB:2.80:Inventory Tracking And Reconciliation cj


    Issue resolved and the problem was identified

  • RELEVANCY SCORE 2.79

    DB:2.79:Counting Numnber Of Cells In A Table That Have The Same Value a8


    Is there a way/formula to do this, other than they way I'm doing it now, which is by using COUNTIF? In a table with 1000 rows for instance, let's say the values in the A column are what I'm keying off. I would like a count of all the cells in the A column that have the word "example" as their value. Right now for each unique value in a cell in the A column I am manually creating a COUNTIF(A1:A1000,"value i'm matching against) but if there are many unique values in the A column, it's quite laborious. Ideally I'd like to just have a table generated that gives me the top 5 or top 10 most occurring cell values in the A column in the table. What's the best way to do this?

    DB:2.79:Counting Numnber Of Cells In A Table That Have The Same Value a8

    So for instance, for a column like this:
    1
    1
    1
    2
    2
    4
    5
    I want a way to get back the number of times 1 appears in the list (3), the number of times 2 appears in the list (2), 4 (1), 5 (1), and so on. If I do a COUNTIF and there are thousands of rows, I have to manually put the matching string in each one.

  • RELEVANCY SCORE 2.79

    DB:2.79:Adding A Unique Constraint To An Existing Column With Duplicate Values ss


    Hello Oracle Forums, I have a table that contains 11,901 rows. I tried to define the UNIQUE constraint on the xcode column but received an error: the column already contain duplicate values! I issued following SQL 'SELECT COUNT(*) FROM states;' the result is 11,901 and 'SELECT COUNT(DISTINCT (xcode)) FROM states;' the result is 11,680. Hence to enforce this unique key I must remove the 221 rows. Please how can I achieve this?

  • RELEVANCY SCORE 2.79

    DB:2.79:How Do I Select Unique Entrys In A Data Bound Combobox? 97


    I have a combobox bound to a column in a table. The column entries contain duplicates which are then reflected into the combobox as selections. Is there a property I can assign to the combobox that will have it only select unique entries from the table? Thanks!

    DB:2.79:How Do I Select Unique Entrys In A Data Bound Combobox? 97

    Many thanks to you both for the replies! Much appreciated !

  • RELEVANCY SCORE 2.76

    DB:2.76:Sum Up All Entries In A Column ck


    Hi,

    this may be very basic. I have a large list of entries, where some of the entries are similar. I want to count all entries and sum them up and show the number on just one line.

    I have tried the COUNT(exp) but this counts all the DIFFERENT entries, not all entries.

    Any ideas?

    Regards
    BJ

  • RELEVANCY SCORE 2.76

    DB:2.76:Counting Two Columns Of Data 8x


    Hello,

    I have a spreadsheet with account names incolumn Aand the year the account ran inColumn B. There are over 260,000 entries in column A and only six unique entries in column B (years2008through 2013).

    The 260,000 account names incolumn A are not unique. there are many duplicates. For example let's say theXYZ Corp had 500 orders in 2013, 650 orders in 2010 and 50 orders in 2008.

    I would like to create a pivot table pivotthat shows how many years each account placed orders, not the total numbers of orders. For example, I would like to show the number "3"for theXYZ Corp. because they placed orders in three years. Whenever I use
    the "Count" value setting in my pivot table, it shows the total number oforders placed not the total years.

    Can I accomplish this in the pivot table or do I have to add another column of data that counts the number of years before I pivot?

    Any help would be greatly appreciated.

    Thanks.

    Chris

    DB:2.76:Counting Two Columns Of Data 8x

    Benishiryo,

    Thank you for responding to my request. Unfortunately, I am using Excel 2007. The company I work for is very slow to invest in software upgardes. It makes my job challenging.

    Thanks anayway.

    Chris

  • RELEVANCY SCORE 2.75

    DB:2.75:Look Up Data In Two Sheets And Return Unique Count xs


    Hi,
    I have three sheets; one is a class roster, the second is a list of employees and their departments, and the third is the summary page.
    On the class roster, an employee may be listed multiple times depending on how many classes they have taken.
    I need to supply a count of how many people in each department took classes.
    So I need to look up the department for each person but then only count the unique number of employees.

    Class roster
    Joe Brown excel 1/2/14
    Sally Green excel 1/2/14
    Sally Green word 1/2/14
    Joe Brown visio 1/15/14
    Vicky Black visio 1/15/14
    Sam White access 1/21/14
    Sally Green access 1/21/14
    Sam White lync 1/15/14

    Employee List
    Joe Brown Accounting
    Sally Green Admin
    Vicky Black IT
    Sam White Admin

    Summary

    Type of Class #of classes
    Total Emp *Acctg *Admin*IT

    Office Classes 5
    4 1
    2 1

    *This is where I need the formula. How do I get a count of 1 for accounting, 2 for admin and 1 for IT?

    Just FYI - I cannot use a helper column in the class roster tab because we need someone who doesn’t know excel to just copy the class roster data in each month.

    Thank you very much!

  • RELEVANCY SCORE 2.73

    DB:2.73:Count Unique Values In A Column When Conditions In A Separate Column Are Met mj


    Let's say I have twocolumns that look like this:

    AB
    red 1
    red 4
    green 2
    blue4
    red 3

    By glancing at this table, you can see that there are 4 unique values in column B. What I need is a formula that will count how many unique values exists in column B ifthe corresponding cell incolumn A says"red". I can't quite figure this one out. Any
    suggestions?

    DB:2.73:Count Unique Values In A Column When Conditions In A Separate Column Are Met mj

    I have been searching for days how to do this and it is fabulous...thanks, nice and simple, works like a charm.

  • RELEVANCY SCORE 2.73

    DB:2.73:Counting Unique Values In A Column sa


    Hello,
    I have column in a Numbers spreadsheet that contains around 5000 values (they are ip addresses e.g. 155.69.160.78). There is one value per row. I need to count the number of unique addresses in the column. How can I do this please?
    KE

  • RELEVANCY SCORE 2.72

    DB:2.72:Count In Numbers - Unique Entries On A List ap


    I'm trying to figure out how to calculateunique entries on a list in Numbers:Fx:Coca ColaVBBruntCoca ColaModern talkingCoca ColaCoca ColaModern talkingIt has:Total entries: 8Unique entries: 4Total entries is simple with the COUNTA,but I just can't figure out how to count theunique entries.Hope any one can help.(Sorry no screen dump, couldn't press insertfor some reason)

    DB:2.72:Count In Numbers - Unique Entries On A List ap

    Jakob,You can move the "extra" columns to another table and place the table "out of the way" OR you can hide the extra column by clicking on the contextual menu for that column and selecting "Hide Column"

  • RELEVANCY SCORE 2.71

    DB:2.71:Query In Foreign Key ad


    When we create a foreign key is it mandatory the referred column should be a Primary or Unique Key. Can't I refer any column without any unique key in it.

    DB:2.71:Query In Foreign Key ad

    user13024762 wrote:
    When we create a foreign key is it mandatory the referred column should be a Primary or Unique Key. Can't I refer any column without any unique key in it.no, you can't

  • RELEVANCY SCORE 2.70

    DB:2.70:Into Bulk Error In Cursor pd


    Hi, I am getting bulk error in cursor, can anybody suggest

    CREATE OR REPLACE procedure magazine_test
    is
    a varchar2(20);
    cursor c2_lifetrends is
    select
    count(unique(case when last_order_dt=sysdate-45 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) one_month1,
    count(unique(case when last_order_dt=sysdate-105 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) three_month1,
    count(unique(case when last_order_dt=sysdate-195 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) six_month1,
    count(unique(case when last_order_dt=sysdate-380 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) twelve_month1,
    count(unique(case when BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) recorded_videos,
    count(unique(case when last_order_dt=sysdate-45 and VETERAN='Y' then a.individual_id end)) one_month_2,
    count(unique(case when last_order_dt=sysdate-105 and VETERAN='Y' then a.individual_id end)) three_month_2,
    count(unique(case when last_order_dt=sysdate-195 and VETERAN='Y' then a.individual_id end)) six_month_2,
    count(unique(case when last_order_dt=sysdate-380 and VETERAN='Y' then a.individual_id end)) twelve_month_2,
    count(unique(case when VETERAN='Y' then a.individual_id end)) all_active_VETERAN,
    count(unique(case when last_order_dt=sysdate-45 and OWN_A_CAT='Y' then a.individual_id end)) one_month_3,
    count(unique(case when last_order_dt=sysdate-105 and OWN_A_CAT='Y' then a.individual_id end)) three_month_3,
    count(unique(case when last_order_dt=sysdate-195 and OWN_A_CAT='Y' then a.individual_id end)) six_month_3,
    count(unique(case when last_order_dt=sysdate-380 and OWN_A_CAT='Y' then a.individual_id end)) twelve_month_3,
    count(unique(case when OWN_A_CAT='Y' then a.individual_id end)) all_active_OWN_A_CAT
    from fact_mke_pub_product a,fact_cust_experian_profile b
    where a.individual_id=b.individual_id
    and pub_status_cd='A' and last_role_cd in('1','2','3');
    --and a.individual_id in(select import_individual_id from unicarunus.mke_lr_net_seg);
    c2_rec c2_lifetrends%rowtype;

    begin

    open c2_lifetrends;
    fetch c2_lifetrends into c2_rec;
    a:=c2_rec.one_month1;
    close c2_lifetrends;

    commit;

    end;

    DB:2.70:Into Bulk Error In Cursor pd

    Hi Vivek,
    My query is too large when I use only

    select
    count(unique(case when last_order_dt=sysdate-45 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) one_month1,
    count(unique(case when last_order_dt=sysdate-105 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) three_month1,
    count(unique(case when last_order_dt=sysdate-195 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) six_month1,
    count(unique(case when last_order_dt=sysdate-380 and BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) twelve_month1,
    count(unique(case when BUY_PRE_RECORDED_VIDEOS='Y' then a.individual_id end)) recorded_videos

    then procedure is creating without error but when I use previous query it shows an error

    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

    := . ( @ % ;

    I also used FOR loop and geting same error

  • RELEVANCY SCORE 2.70

    DB:2.70:Distinction Between Unique And Distinct z7


    I faced a very strange problem when using UNIQUE and DISTINCT inside a view definition.

    This is the problem summary

    1. Create a table "test" having the following structure

    Name Null? Type
    ------ ----- --------------
    COL1 VARCHAR2(1)
    COL2 VARCHAR2(1)
    BLOCKS NUMBER

    2. Inserted the following data in the "test" table

    SQL select * from test;

    COL1 COL2 BLOCKS
    -------- --------- -------------
    A B 10
    A C 20
    B B 10

    3. Created a view "test_view" on the "test" table

    SQL create or replace test_view as select UNIQUE col1, col2, blocks from test;

    4. When the view is queried we get the following output which is the same as when the table is queried

    SQL select * from test_view;

    COL1 COL2 BLOCKS
    -------- --------- -------------
    A B 10
    A C 20
    B B 10

    5. Now if I run the following query

    SQL select count(*) from test_view;

    I get a result of 2, whereas as the "SELECT *" query returns 3 records. Why is the result different ???

    6. Now if I run the following query

    SQL select col1 from test_view;

    COL1
    --------
    A
    B

    Again one of the rows goes missing. It so seems that the column mentioned in the outer query is mapped into the UNIQUE clause of the view definition.

    7. Now if I run the following query

    SQL select sum(blocks) from test_view;

    I get an output of 30, whereas I should have expected an output of 40. Again it is seen that only distinct values from the BLOCKS column is added.

    8. Now if the view was recreated as

    SQL create or replace test_view as select DISTINCT col1, col2, blocks from test;

    9. If I run the above queries, I get the results as expected

    SQL select count(*) from test_view;

    COUNT(*)
    ------------------
    3

    10. If the following query is run

    SQL select col2 from test_view;

    COL2
    ---------
    B
    C
    B

    11. If the following query is run

    SQL select sum(blocks) from test_view;

    SUM(BLOCKS)
    -------------------------
    40

    I get the correct results when using DISTINCT instead of UNIQUE. Even though in Oracle both UNIQUE and DISTINCT are synonymous and should perform the same operation of getting unique/distinct result sets.

    Why is it that they behave so differently when used in the above context ???

    Does the behavior of the UNIQUE command change when used with other Oracle objects ???

    DB:2.70:Distinction Between Unique And Distinct z7

    You right, John .. I did run the queries on the 8.1.7 release. I have fixed the queries to use DISTINCT instead of UNIQUE

    Thanks,
    Glen M

  • RELEVANCY SCORE 2.69

    DB:2.69:Row Count Validation - Slt Replication c7



    Dear All,

    I am replicating VBAP table from ECC to HANA using SLT data provisioning technique. but when i try to validate the record count, both ECC and HANA table record count is not matching. tried with TCURR table but result is same.

    In ECC, i am using SE16 and in HANA using column "No of Entries" from "run time information' tab.

    Any clue why it is like that? how can i validate the record count?

    Thanks,

    Raghu

    DB:2.69:Row Count Validation - Slt Replication c7


    duplicated post:

    http://scn.sap.com/thread/3378708

  • RELEVANCY SCORE 2.69

    DB:2.69:Totals Values On Task View Incorrect When Any Column Filtered 89


    Hi,

    I have modified various views on the default Tasks list to count the number of entries and summarise the Days to Complete column.
    However, when I filter a column, the overall totals are still shown - they do not adjust to exclude values related to columns not included in the filter. This worked on SharePoint 2010.
    Is there any way of resolving this?

    DB:2.69:Totals Values On Task View Incorrect When Any Column Filtered 89

    Hi,

    I have modified various views on the default Tasks list to count the number of entries and summarise the Days to Complete column.
    However, when I filter a column, the overall totals are still shown - they do not adjust to exclude values related to columns not included in the filter. This worked on SharePoint 2010.
    Is there any way of resolving this?

  • RELEVANCY SCORE 2.69

    DB:2.69:Counting Unique Values 99


    Can anyone tell me how to count the number of unique values in a column. The cells contain a combination of numbers and letters

    Thanks

    DB:2.69:Counting Unique Values 99

    I *think* I have a partial understanding. You seem to be looking for the count of each additional instance of a value after its first instance.

    So, what values would you want for this data set?:
    A
    A
    B
    B
    B
    1
    3
    3
    3
    3
    C
    C
    2
    2
    2
    2

    Count of "duplicate" letters: 4?
    Count of "duplicate" numbers: 6?

    Hi Ron,
    From your above data, i want the below to be counted for numbers letters respectively.

    A
    Unique

    A
    Duplicate

    B
    Unique

    B
    Duplicate

    B
    Duplicate

    1
    Unique

    3
    Unique

    3
    Duplicate

    3
    Duplicate

    3
    Duplicate

    C
    Unique

    C
    Duplicate

    2
    Unique

    2
    Duplicate

    2
    Duplicate

    2
    Duplicate

    I hope this makes some sense.

    Warm regards Dharmesh

    Number of numbers or letters: =COUNTA(A1:A20)
    Number of unique numbers or letters: =SUMPRODUCT((A1:A20"")/COUNTIF(A1:A20,A1:A20""))
    Number of numbers: =COUNT(A1:A20)
    Number of unique numbers: =SUMPRODUCT((ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    Number of letters: =COUNTA(A1:A20)-COUNT(A1:A20)
    Number of unique letters: =SUMPRODUCT(--(A1:A20""),NOT(ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    From these formulas you can calculate the number of duplicates of different types by subtractions of the respective formulas above, like this:
    Number of duplicate numbers or letters: =COUNTA(A1:A20)-SUMPRODUCT((A1:A20"")/COUNTIF(A1:A20,A1:A20""))
    Number of duplicate numbers: =COUNT(A1:A20)-SUMPRODUCT((ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    Number of duplicate letters: =COUNTA(A1:A20)-COUNT(A1:A20)- SUMPRODUCT(--(A1:A20""),NOT(ISNUMBER(A1:A20))/COUNTIF(A1:A20,A1:A20""))
    Hope this helps / Lars-Åke

  • RELEVANCY SCORE 2.69

    DB:2.69:How Do I Recognize And Treat Seperately Entries In A Text Box? ms



    I've placed a text box in a report that is populated by data entries that are seperated by a semicolon. Each entry is unique and distinct. I want to try to count and group these individually, but since they exist in a text box seperated by a semicolon, how can I treat them as unique? Here is an example:

    02 Inadequate Policies and/or Procedures;01 Lack of Policies and/or Procedures;05 Inadequate Separation of Duties;10 Financial Systems Deficiency;

    DB:2.69:How Do I Recognize And Treat Seperately Entries In A Text Box? ms


    Hi,

    Whether the values is fixed ie, you will get a string with 5 values which is :;: seperated.

    If it is fixed you can use a way.

    for ex; you have a string of 5 values.

    first formula u write : Stringvar

    Variablename := split(Fieldname,';')[0]

    like that five formula.

    Then you can take the count(Variablename).

    Regards,

    Sathish

  • RELEVANCY SCORE 2.68

    DB:2.68:Unique Column Consraint fm


    What is a unique column constraint

    DB:2.68:Unique Column Consraint fm

    I'm assuming you have access to Oracle documentation:

    http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#i1034458

    http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm#sthref3160

  • RELEVANCY SCORE 2.68

    DB:2.68:Unique Index Violation When The Data Does Not Have Any Violation sj


    I have a table with a 7 column unique index

    I do select unique index columns,count(*) from queue_table group by columns having count(*) 1;

    I get no rows.

    When I try and insert into working_table which has the unique index in place, I get the error

    Edited by: amin_adatia on 3-Dec-2012 7:30 AM

    PLease ignore this ! Bad code on my part. Sorry.

  • RELEVANCY SCORE 2.67

    DB:2.67:Count Of Unique Text-Value Cells In One Column That Also Have Specific Text Values In Two Other Columns 9m


    I have a worksheet that has 28 columns, with data in almost 11,000 rows.

    Column D is "name" which is a text string
    Column X has text values of either Yes or No
    Column AA has a 4 character text string (e.g. ABCD - only 6 possible values in this column, representing business units)

    I need to count the number of unique values in D for each unique value in AA, e.g. ABCD has 5 unique names, EFGH has 30 unique names, etc. This is UniqueNamesPerBusinessUnit

    Next, I need to count the number of unique values in D that also have the value Yes in X, for each value in AA. This is UniquePositiveNamesPerBusinessUnit

    This will allow me to then get a "score" percentage of UniquePositiveNamesPerBusinessUnit/UniqueNamesPerBusinessUnit

    I'm more than willing to make intermediate calculations in separate cells - one for each business unit for UniqueNames and then UniquePositiveNames?

    I've tried samples that use matches within frequency, but only ever get #N/A values when copying the formula from the office site examples, even just to get a unique list of names, but I need to also consider the extra parameters of business unit, and then
    the Yes/No in column X, and at that point my eyes glaze over.

    Ideas?

  • RELEVANCY SCORE 2.67

    DB:2.67:How To Restrict Qlikview From Removing Duplicates? mk



    Other than adding a unique column like Rowno() in the table is there a way to keep the duplicate entries in a table?

    Warm Regards,Dawar

    DB:2.67:How To Restrict Qlikview From Removing Duplicates? mk


    Take the key you'd want in the table and do a count of the key in a list box

  • RELEVANCY SCORE 2.67

    DB:2.67:Count Uniue Values That Are Not In A Nother List pa


    Hi, I need to count the unique values in Column A that do not appear in Column B. Please help?

    DB:2.67:Count Uniue Values That Are Not In A Nother List pa

    Hi, Consider these,

    A2:A100 is first list
    B2:B20 is 2nd list

    So, this should give the count of unique values in A2:A100 which is NOT in B2:B20

    =SUM(IF(ISNA(MATCH(A2:A100,B2:B20,0)),IF(A2:A100"",(IFERROR(MATCH(A2:A100,A2:A100,0),"")=(ROW(A2:A100)-ROW(A2)+1))+0)))

    This is an Array Formula, so must hit CTRL+SHIFT+ENTER, rather than just ENTER

  • RELEVANCY SCORE 2.66

    DB:2.66:Count Text In The Column pj



    Hi All,

    I am trying to count no of entries in a column respective to different user, but QV is counting the blank entries also. how to solve this issue?

    UserEntryayesayesa byesb b cyescyescyesc c

    Now when i try to take a count using straight chart, i am getting :

    a - Count (3) instead of 2.

    b - Count (3) instead of 1

    c - Count (5) instead of 3

    When i export the data into excel and try to do it then again the same thing happens on excel sheet also.

    Thanks Regards

    Jyothish KC

    DB:2.66:Count Text In The Column pj


    Here is my solution:

    Count

    ({Entry ={yes}}User)

  • RELEVANCY SCORE 2.66

    DB:2.66:Don't Want To Sort Variable Values In Formula Function Reading From Exit c9



    Hi,

    I've a variable defined on Characteristic "CUSTOMER" of type user exit. let'z say, when executed, Funtion Module provides the following values.

    2500

    3000

    1200

    I've a formula function that uses CUSTOMER variable values for calculations.

    But, while reading the CUSTOMER values, formula is sorting the values and reading the values. But, I need to maintain the order as it is provided by the Function Module.

    formula function is as follows.

    DATA COUNT TYPE I.

    DATA ENTRIES TYPE I.

    DATA CUST TYPE 0CUSTOMER.

    ENTRIES = VARC('CUSTOMER').

    COUNT = 1.

    IF ENTRIES 0.

    DO.

    CUST = VARI('CUSTOMER',COUNT).

    some calculations here ******

    COUNT = COUNT + 1.

    IF ENTRIES COUNT.

    EXIT.

    ENDIF.

    ENDDO.

    ENDIF.

    In the debugging mode, CUSTOMER values are being read in this order

    ************

    EXIT FORMULA

    ************

    2500 1200

    3000 2500

    1200 3000

    where, EXIT column displays the order of CUSTOMER values ouput reading from the Function Module.

    where, FORMULA column displays the order of CUSTOMER values reading witin DO loop of formula function.

    How do i maintain the order of CUSTOMER values in the FORMULA, same as the order sent by user exit function module?

    Appreciate your ideas.

    DB:2.66:Don't Want To Sort Variable Values In Formula Function Reading From Exit c9


    Hi Hari,

    I'm sorry, but I'm not be able to give you some help at this moment for your scenario...hoping for others contributions,

    bye,

    Roberto

  • RELEVANCY SCORE 2.66

    DB:2.66:Rtf - Grouping Two Fields To Display The Their Unique Totals 88


    Hello Experts,

    I have an issue creating a rtf report that I've been trying to overcome for the past week. I'm having trouble grouping two different columns and then getting the sum of these unique column combinations. I'm hoping somebody can shed some light on how to go about this issue.

    My XML looks like this:

    ROW
    INFO CODE1INFO CODE
    SEGMENTB/SEGMENT
    RECORD OUNT_254/RECORD COUNT
    /ROW
    ROW
    INFO CODE1INFO CODE
    SEGMENTB/SEGMENT
    RECORD OUNT_300/RECORD COUNT
    /ROW
    ROW
    INFO CODE1INFO CODE
    SEGMENTS/SEGMENT
    RECORD OUNT_95/RECORD COUNT
    /ROW
    ROW
    INFO CODE2INFO CODE
    SEGMENTB/SEGMENT
    RECORD OUNT_745/RECORD COUNT
    /ROW
    ROW
    INFO CODE2INFO CODE
    SEGMENTS/SEGMENT
    RECORD OUNT_15/RECORD COUNT
    /ROW
    ROW
    INFO CODE2INFO CODE
    SEGMENTS/SEGMENT
    RECORD OUNT_85/RECORD COUNT
    /ROW

    My desired outcome is to have a table that looks like this:

    INFO CODE / SEGMENT / RECORD COUNT
    1 / B / 554
    1 / S / 95
    2 / B / 745
    2 / S / 100

    I am able to group and get the correct record count when I just have the INFO CODE and the RECORD COUNT. However, I am having a lot of trouble when I add in the new SEGMENT field that can have multiple segments for each INFO CODE. Any help would be much appreciated.

    Thanks,
    Jeff

    DB:2.66:Rtf - Grouping Two Fields To Display The Their Unique Totals 88

    This is exactly what I was looking for. Thank you for your help

  • RELEVANCY SCORE 2.66

    DB:2.66:Unique Count For A Pivot Table 8d


    I see a bunch of posts on this but I am unsure it resolves my issue

    Column B has the customer number
    Column F has the Parts they ordered
    Column AA has the Country they are in

    There is a column "e" that has the part quantity (not sure if this will be needed)

    What I want know is, the unique number of customers (B) for a specific country (AA) that purchased parts

    example, If a customer purchased the same part 3 times in the US I would expect to see 1 for the customer count and 3 for the number of the same part

    A customer could show on a single line item qty of 3 of a part or they could show up as 3 different line items.

    Any suggestions?

    DB:2.66:Unique Count For A Pivot Table 8d

    Hi,

    You may refer to my solution at the follolowing link -
    http://www.ashishmathur.com/count-uniques-with-conditions/

    Hope this helps.

  • RELEVANCY SCORE 2.65

    DB:2.65:I Want A General Query For Retrieveing Below Database Objects cx


    plz give me a General Query or function for retrieveing below things related to microsoft sql server 2000 or more versions....
    i want
    schema,table name,view ,column count,primary key,primary key schema,composite primary key,foreign key,unique key count,identity column,table size ,remarks,row count,primary key count,foreign key count,foreign key column count,primary key column count,unique
    key count,unique key column count,computed column count,identity column count

    thank u...

    DB:2.65:I Want A General Query For Retrieveing Below Database Objects cx

    i m not saying that u give me a big query ....but i also done with big query for retrieveing above things if u give me idea how it reduce ...nd ya u say copy paste so that things only do lossers ok !!!

  • RELEVANCY SCORE 2.65

    DB:2.65:Count If Else ps



    Hi everyone, Along with some other fields I have a table in my report with the below two columns.

    The ACCOUNT NUMBER in the table repeats, but is unique when the DECISION column is factored in.

    I'm trying to find a way to count "Decision" once per Account number.

    In a way I need to be able to do some kind of compare between all the decision before per a unique account number and count it base on value of the decision.

    A unique account number can have up to three (3) instances (Decsions) in the table

    So for example

    If DECISION = "DENY" for any instance of that specific account number, ignore every other instance and add 1 to the DENY count bucket

    Else if DECISION = "PAY" for all instances of a specific account number add 1 to the PAY count bucket

    Else if DECISION "DENY" but Decision = "CHANGE" for any instances of a specific account number add 1 to the CHANGE count bucket

    So in the end my count should look like this:

    DENY = 2

    PAY = 1

    CHANGE = 2

    I've tried using =Count([ACCOUNT NUMBER]) In([DECISION]) But that gave me higher numbers that it should. For example PAY ended up equaling 3 when it should have been 1. And i can't seem to get the sytax right for my if statments for all other attempts.

    Any assistance will be greatly appreciated!!!

    DB:2.65:Count If Else ps


    Hey Rakesh, Thanks! that worked. REALLY appreciated the detail response!!!

  • RELEVANCY SCORE 2.65

    DB:2.65:Distinct Select On Varchar2 Column cs


    Hi,

    I have a unique problem - (no pun intended) - pls bare with me while I explain...

    I have the following table (tbl_party) :

    p_num VARCHAR2(30)
    p_name VARCHAR2(100)
    p_cert NUMBER
    i_cat NUMBER
    p_stat DATE

    Test 1.
    When I do a select count(distinct(p_num)) from tbl_party;
    Return 6298 rows

    Test 2.
    When I do a select distinct(p_num) from tbl_party;
    Return 6298 rows

    Test 3.
    When I do a select distinct(p_num), p_cert from tbl_party;
    Return 6300 rows???

    Can some1 pls enlighten me if there is a limitation on distinct/unique when using it on VARCHAR2??

    Thanks in advance.

    Jaco

    DB:2.65:Distinct Select On Varchar2 Column cs

    Ah I see.
    Thanks for the quick response!

    Best regards,
    J

  • RELEVANCY SCORE 2.65

    DB:2.65:How Do I Use Countifs To Count Discrete Numbers pm


    For the table below, I am trying to count the number of discrete entries in column 3, based on a selected variable from columns 1 and 2. So for example if I select "MM" from column 1 and "B" from column 2 how many discrete numbers are there in column 3.
    I know how to get the total number of entries in column 3 using countifs, but can't do this. Can you help?

    Thanks

    DB:2.65:How Do I Use Countifs To Count Discrete Numbers pm

    You are welcome. If my reply helped, please mark it as Answer.

  • RELEVANCY SCORE 2.65

    DB:2.65:Counting The Quantity Of Unique Text Values When Some Cells May Be Blank km


    I have data that repeats in cells of the same column. Some of the cells are blank. I want to count the number of unique cell entries but ignore the blank cells.
    Example:
    I have cells in column A as an examplewith the following
    Jim
    Jim
    Tom
    Tom
    Dave
    Dave
    Ted
    Barney
    Leo
    "blank cell"

    I want to have MS excel count the # of unique enties, but ignore the blank cells(the answer should be 6)
    How can I do this?

  • RELEVANCY SCORE 2.63

    DB:2.63:Partial Sum In Pivot Table pd



    Hi everyone,

    I have a small issue (perfectly understandable though) in QV with parital sums in my pivot table (see screenshot below).

    The number in the last column (# of tags) is based on an expression. The expression counts unique codes using simple count(fieldName). All good so far.

    However, some of the codes have been detected on multiple days - this causes the partialSum to be indeed a count of unique codes but not a sum of the numbers above it. Because it is an expression, I cannot define accumulation / total mode. I also cannot switch to "calculated dimension", as count(fieldName) delivers //calculation error when used as dimension.

    How can I solve this? Is there a way to solve this?

    Thanks in advance!

    DB:2.63:Partial Sum In Pivot Table pd


    Ok, in case anyone else runs into this, the solution is is not really dimensionality although you may use it - it will just not do anything.

    The actual solution is this:

    http://community.qlik.com/message/189352#189352

    ..that is just one example, there are many other posts of ppl who run into issues with (partial) sums in pivot tables.

    I might have done a lousy job explaining what was wrong, for that I apologize.

    Thanks,

    Jan

  • RELEVANCY SCORE 2.63

    DB:2.63:Cluster Count 83



    There is this table uniqcust which will have unique id's

    and other table cluster which will have id and cluster.

    we select only those records that are common in uniqcluster and cluster tables

    uniqcust table:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    cluster table:

    1 12345

    2 12345

    3 12345

    4 12345

    5 12345

    6 12345

    7 12345

    8 23456

    9 23456

    10 23456

    now when the condition is if cluster count is 5 ,output must be:

    1 5 6 2

    The first column 1 is the minium id of cluster 12345 and their cluster count is 5 and after that threshold the next minimum id is 6 and it has cluster count of 2 (which are 6 and 7).

    only one record is displayed since their counts belong to 12345 cluster.

    Scenario 2

    cluster table:

    1 12345

    1 23456

    2 12345

    3 12345

    4 12345

    5 12345

    6 12345

    5 23456

    6 23456

    7 23456

    8 23456

    9 23456

    10 23456

    output:

    1 5 6 1

    1 5 9 2

    The first column 1 is the minium id of cluster 12345 and their cluster count is 5 and after that threshold the next minimum id is 6 and it has cluster count of 1.

    second row,The first column 1 is the minium id of cluster 23456 and their cluster count is 5 and after that threshold the next minimum id is 9 and it has cluster count of 2 (which are 9 and10).

    DB:2.63:Cluster Count 83


    There is this table uniqcust which will have unique id's

    and other table cluster which will have id and cluster.

    we select only those records that are common in uniqcluster and cluster tables

    uniqcust table:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    cluster table:

    1 12345

    2 12345

    3 12345

    4 12345

    5 12345

    6 12345

    7 12345

    8 23456

    9 23456

    10 23456

    now when the condition is if cluster count is 5 ,output must be:

    1 5 6 2

    The first column 1 is the minium id of cluster 12345 and their cluster count is 5 and after that threshold the next minimum id is 6 and it has cluster count of 2 (which are 6 and 7).

    only one record is displayed since their counts belong to 12345 cluster.

    Scenario 2

    cluster table:

    1 12345

    1 23456

    2 12345

    3 12345

    4 12345

    5 12345

    6 12345

    5 23456

    6 23456

    7 23456

    8 23456

    9 23456

    10 23456

    output:

    1 5 6 1

    1 5 9 2

    The first column 1 is the minium id of cluster 12345 and their cluster count is 5 and after that threshold the next minimum id is 6 and it has cluster count of 1.

    second row,The first column 1 is the minium id of cluster 23456 and their cluster count is 5 and after that threshold the next minimum id is 9 and it has cluster count of 2 (which are 9 and10).

  • RELEVANCY SCORE 2.63

    DB:2.63:Select Statement With Distinct And Count ak


    I am trying to select from a column of IP's the number of unique values PLUS how many values fall under each unique value. So bascially how many duplicates of each unique IP.

    Example

    10.2.3.4 - 4 occurances
    10.4.5.3 - 1 occurance

    etc.....can you do this?

    DB:2.63:Select Statement With Distinct And Count ak

    I had originally tried this but I guess not allowing for the entire scroll so when I thought there was only 100 or so rows there was much much more.

  • RELEVANCY SCORE 2.63

    DB:2.63:Countif Unique Values Under Two Criteria 31


    Ok, so I guess I ran into another problem. Is there a way to count unique values that are also under another column of criteria?

    Example

    RED1
    on

    RED1
    off

    RED1
    on

    RED2
    on

    RED2
    off

    RED2
    off

    RED3
    on

    RED3
    off

    RED3
    off

    How can I count all the colors that are unique and off? Like here the answer should be 3 and not 5 since there are only 3 unique first column entries.

    Thanks,
    Evil Spleen

    DB:2.63:Countif Unique Values Under Two Criteria 31

    RED1
    on
    new

    RED1
    off

    RED1
    on

    RED2
    on

    RED2
    off

    RED2
    off

    RED3
    on

    RED3
    off
    old

    RED3
    off

    OK. I tried with the above sample data...

  • RELEVANCY SCORE 2.62

    DB:2.62:Unique Count For Sharepoint List ? ca


    Hi,
    i want to displaycount for sharepoint List items
    For example:
    ColumnA Column B Column C
    10-0032-01JDHBTY09January
    10-0032-01BHNUY010February
    11-0201-09KJKJUYHJ9March
    11-0201-09HNGTY100April
    I am looking for count for column A and it is giving me 4 instead of 2.
    Questions:- 1. Is there a formula I can use in the sharepoint to create a column to capture unique numbers from the data in column A as above?
    it would be great if you help me in sloving this problem..
    Thanks in advanceThanks & Regards, Sruthi

    DB:2.62:Unique Count For Sharepoint List ? ca

    Did you try using LINQ?
    E.g.
    var designations = (from SPListItem employee in employees select employee[Employee Designation].ToString()) .Distinct() .ToList();

  • RELEVANCY SCORE 2.62

    DB:2.62:Counting Unique Entries pd


    Dear all,
    The below data contains some duplicates and unique entries. I am trying to count the duplicate unique entries respectively?

    FDR1224

    FDR1224

    FDR1224

    FDR1224

    FDR1225

    FDR1225

    FDR1226

    FDR1226

    FDR1226

    FDR1226

    FDR1226

    FDR1226

    FDR1226

    FDR1227

    FDR1227

    FDR1227

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    FDR1228

    In the above sample data, the number of Duplcate entries is 23 number of unique entries is 5. I am trying to find a formula to calculate the same.

    Kindly suggest!

    DB:2.62:Counting Unique Entries pd

    There are several array formulas to do this, depending on the nature of the data. In the formulas below, it is assumed that the data to test is in the range A1:A10. If there are no blank cells within the data, use
    =SUM(1/COUNTIF(A1:A10,A1:A10))

    If the data contains only numeric values or blanks, use
    =SUM(N(FREQUENCY(A1:A10,A1:A10)0))
    If the data contains a mix of alpha-numeric values, numeric values, and blank cells, use
    =SUM(1/IF(A1:A10="",1,(COUNTIF(A1:A10,A1:A10))))-COUNTBLANK(A1:A10)

    Note thatthese are allarray formulas, so you MUST press CTRL SHIFT ENTER
    rather than just ENTER when you first enter the formula and whenever
    you edit it later. If you do this properly, Excel will display the formula
    in the formula bar enclosed in curly braces { }. You don't type in the
    braces -- Excel puts them in automatically. The formulas will not return the
    correct answer if you do not use CTRL SHIFT ENTER. For more information
    about array formulas, see
    http://www.cpearson.com/Excel/ArrayFormulas.aspx.

  • RELEVANCY SCORE 2.62

    DB:2.62:Set Unique Entries In Child Tables? mk


    Hi,

    OIM 11g here. I have built a process form with its attached child table for a custom connector i have made. Now i noticed that the child table accepts duplicate entries. How can i make the child table entries unique, as they are in the AD user groups child table?

    thx in advance.

    DB:2.62:Set Unique Entries In Child Tables? mk

    I have always coded these checks into my custom code whenever i maninuplate child table data. When adding, i always check the existing and when deleting, i always check if the value exists that i'm removing. I would suggest you write a piece of code that does the same and keep it in your library of re-usable code.

    You could try going to the database and making the field for the name a unique field. I don't know what the outcome will be from doing so.

    -Kevin

  • RELEVANCY SCORE 2.62

    DB:2.62:Count Distinct With More Then 1 Column cp


    I have a total amount of cases, CASE_NO

    count(distinct CASE_NO) gives me the amount of unique cases

    When I want the total amount of patients I use this

    Select count(CASE_NO, PAT_NO, PAT_GROEP_NO) from table1

    But it doens't work.. does anyone know how to use a count distinct on more then 1 column?

  • RELEVANCY SCORE 2.62

    DB:2.62:Duplicates Validation On Tabular Form af


    I have a wizard built tabular form. This displays 2 pieces of information, Phase ID and Phase Description, which the user can either modify or they can add new records. At the database level, I have a unique constraint on Phase ID (even tho it's called Phase ID this is not the primary key). Right now, when the user tries to enter a duplicate Phase ID, they get taken to an error page with the ugly Oracle contraint violation error. What I'm trying to do is create a validation in APEX that checks for duplicates so I can throw a nice, friendly Inline Notification for the user. After searching this forum, I have tried differing variations of the following code:

    DECLARE
    vMatch NUMBER;
    vItem VARCHAR2(1);
    BEGIN
    FOR i IN 1.. HTMLDB_APPLICATION.G_F02.COUNT LOOP
    vItem := HTMLDB_APPLICATION.G_F03(i);
    SELECT COUNT(*) INTO vMatch FROM Phase where Phase_ID = vItem;
    IF vMatch 1 THEN
    RETURN 'Phase ID must be unique.';
    ELSE
    RETURN '';
    END IF;
    END LOOP;
    END;

    Obviously I can't get this to work or I wouldn't be posting my question! Can anybody please help me tweak this to validate duplicate entries or point me in the right direction? Thank you very much!

    DB:2.62:Duplicates Validation On Tabular Form af

    Andy, we are getting closer! I am now getting the nice Inline message when I try to enter a duplicate but also when I enter a non-duplicate! I am going to look at it further and try to tweak it so it is allowing unique entries. Thank you so much for all your help.

  • RELEVANCY SCORE 2.62

    DB:2.62:Saving Validation fz


    I have a table of drawing numbers that have a unique index (no duplicates allowed).
    I have a button that runs the excel importer. When the file is imported, my program checks all entries that are pending to be saved and deletes them if they are already saved
    to the database. However, if there are duplicate entries that are both pending to be saved, that is an unhandled issue and there will be a save error.
    So, in essence, I want my program to also automatically delete any duplicates within the EntityState.Added entries before the program starts to save. Not sure how to go about doing this.

    Here is my current code that properly deletes pending items if they already are saved to the database:
    Private Sub AddOrEditDrawings_Saving(ByRef handled As Boolean)
    ' Checks for duplicate entries before attempting to save.

    Dim countDuplicates = 0 ' Count variable to show amount of entries deleted

    ' Runs through each entry and determines whether or not it was newly added. If newly added,
    ' a check is run to see if that same drawing exists in the database already.
    For pointer = (Me.Drawings.Count - 1) To 0 Step -1
    Dim ptDrawing = Me.Drawings.ElementAt(pointer)
    If (ptDrawing.Details.EntityState = EntityState.Added) Then
    Dim propertyName = ptDrawing.DrawingNumber
    Dim exists = (Me.DataWorkspace.ApplicationData.Drawings.Where(Function(x) x.DrawingNumber = propertyName).FirstOrDefault() IsNot Nothing)

    ' If the newly added entry already exists in the database, it is deleted.
    If (exists = True) Then
    ptDrawing.Delete()
    countDuplicates = 1
    End If
    End If
    Next

    If countDuplicates 0 Then
    ShowMessageBox(CStr(countDuplicates) duplicate entries were found and deleted.)
    End If

    End Sub

    DB:2.62:Saving Validation fz

    Here is the working code. I used three sub procedures. First, there is a button click event handler to use excel import. Secondly, the AddorEditDrawings_Saved() procedure compares the pending drawing names against ones that are already saved into the database.
    The ImportCheck() procedure compares the pending drawings against other pending drawings to ensure that no duplicates are saved to the database.
    This code is designed specifically to only allow one copy of a drawing name in the database, period. It is a great example of how to write code around excel importer to:
    A. Prevent user from entering duplicates manually (unique ID)
    B. Prevent user from uploading entries that have already been saved to the database
    C. Prevent user from uploading a n excel document that has duplicate entries within it.
    Private Sub ExcelImport_Execute()
    ExcelImporter.Importer.ImportFromExcel(Me.Drawings)
    End Sub

    Private Sub AddOrEditDrawings_Saving(ByRef handled As Boolean)
    ' Checks for duplicate entries before attempting to save.
    Dim countDuplicates = 0 ' Count variable to show amount of entries deleted.

    ' Runs through each entry and determines whether or not it was newly added. If newly added,
    ' a check is run to see if that same drawing exists in the database already.
    For pointer = (Me.Drawings.Count - 1) To 0 Step -1
    Dim ptDrawing = Me.Drawings.ElementAt(pointer)
    If (ptDrawing.Details.EntityState = EntityState.Added) Then
    Dim propertyName = ptDrawing.DrawingNumber
    Dim exists = (Me.DataWorkspace.ApplicationData.Drawings.Where(Function(x) x.DrawingNumber = propertyName).FirstOrDefault() IsNot Nothing)

    ' If the newly added entry already exists in the database, it is deleted.
    If (exists = True) Then
    ptDrawing.Delete()
    countDuplicates = 1
    End If
    End If
    Next

    ' Displays total duplicated entries deleted.
    If countDuplicates 1 Then
    ShowMessageBox(CStr(countDuplicates) duplicate entries were found and deleted.)
    ElseIf countDuplicates 0 Then
    ShowMessageBox(CStr(countDuplicates) duplicate entry was found and deleted.)
    End If

    Call ImportCheck()

    End Sub

    Private Sub ImportCheck()
    Dim counter As Integer = 0
    Dim countExcelDuplicates As Integer = 0

    For pointer = (Me.Drawings.Count - 1) To 0 Step -1
    counter = 1 'Custom ID to keep track of incoming drawings (default ID is not assigned until entry is saved).
    Dim ptDrawing = Me.Drawings.ElementAt(pointer)
    If (ptDrawing.Details.EntityState = EntityState.Added) Then
    Dim propertyName = ptDrawing.DrawingNumber
    ptDrawing.DuplicateID = counter ' Assigns property duplicateID to the custom ID counter variable.

    ' Checks if the drawing name exists in the pending entries AND if the counter variable is not the same as the one that the pointer is looking at
    Dim exists = (Me.DataWorkspace.ApplicationData.Details.GetChanges().AddedEntities.OfType(Of Drawing)().Any(Function(x) x.DrawingNumber = propertyName And x.DuplicateID counter))

    ' If the newly added entry already exists in the database, it is deleted.
    If (exists = True) Then
    countExcelDuplicates = 1
    ptDrawing.Delete()
    End If
    End If
    Next

    If countExcelDuplicates 1 Then
    ShowMessageBox(countExcelDuplicates duplicate entries were found and deleted within the Excel file.)
    ElseIf countExcelDuplicates 0 Then
    ShowMessageBox(countExcelDuplicates duplicate entry was found and deleted within the Excel file.)
    End If
    End Sub

    MessageBox.Show(Hello, world.)

  • RELEVANCY SCORE 2.62

    DB:2.62:Thread: Mysql Query Help x8


    I\'ve got a table in MySQL 5.0 that has a column defined as char(20). Even

    though it has a length of 20 each item in the column is never greater than

    9 characters, and each one is unique (this column is the primary key).

    Even though each one is unique it\'s possible two different entries could

    have the first 8 characters repeated and only the 9th is different, or in

    some cases one entry may only have 8 characters total while another has 9

    characters total and the 8 character entry matches the first 8 characters

    of the 9 character entry.

    Given that, does anyone have any idea of how to get a count of what I may

    consider unique: only entries where all 9 characters differ? I would

    imagine this would require the use of both count and distinct.

    -' + '-

    Joe

    Those of you who think you know everything are annoying to those of us who do.

    DB:2.62:Thread: Mysql Query Help x8

    Joseph Marton wrote:

    I\'ve got a table in MySQL 5.0 that has a column defined as char(20).

    Even though it has a length of 20 each item in the column is never

    greater than 9 characters, and each one is unique (this column is the

    primary key). Even though each one is unique it\'s possible two

    different entries could have the first 8 characters repeated and only

    the 9th is different, or in some cases one entry may only have 8

    characters total while another has 9 characters total and the 8

    character entry matches the first 8 characters of the 9 character

    entry.

    Given that, does anyone have any idea of how to get a count of what I

    may consider unique: only entries where all 9 characters differ? I

    would imagine this would require the use of both count and distinct.

    Something like this?

    select count(column) from (SELECT column FROM table group by

    mid(column,1,8)) as test

    Or if you are using the mySQL Query engine and just want one off

    information you can simply execute

    SELECT column FROM table group by mid(column,1,8)

    and once the query is finished you will get a count.

  • RELEVANCY SCORE 2.61

    DB:2.61:Effects Of Running With A Too Small Partition-Count xa


    A customer is running with the default partition count at 257 for 20 nodes in the cluster. Eventually they plan to increase their partition count to the recommended 401, the first prime of 20 squared. Besides less efficient load distribution, are there any other negative effects of running with a too small partition count? Also when I do the math for comparing the partition count of 257 vs 401 for a map of 1 million entries, it does not seem that significant to me. Can the customer just stay with the default setting of 257.

    For 257, 1 million entries are chopped into 257 partitions of ~3890 entries each.
    If you distribute 257 partitions evenly among 20 nodes, 3 nodes will have 12 partitions. 17 nodes will have 13 partitions each- an extra partition of ~3890 more entries (i.e. 257 modulo 20= 17).
    To keep things simple, these figures are not doubled to account for partition backup copies.

    For 401, 1 million entries are chopped into 401 partitions of ~2500 entries each.
    19 nodes will hold 20 partitions each. 1 node holds 21(i.e. an extra partition of 2500 entries.)

    Thanks,
    Kim

    DB:2.61:Effects Of Running With A Too Small Partition-Count xa

    Few partitions (or to be more precise very large partitions - you don't say what the data size is) may result in suboptimal fail over performance.

    If you use the new (Coherence 3.5) splitting backing-maps you will see more contention with fewer partitions (would only be an issue with VERY few partitions per node compared to the number of service threads).

    /Magnus

    Edited by: MagnusE on Sep 7, 2009 12:44 PM

  • RELEVANCY SCORE 2.61

    DB:2.61:How Can I Count Unique Text Cells That May Also Contain Blenk Cells 97


    I have data that looks like the following in a given column
    Mary
    Dave
    Mary
    Mary
    Tom
    Dave
    Jim
    Barney
    "Blank Cell"
    Leo

    I want to be albe to count the number of unique text occurances and also ingnore the blank cells. The answer shjold be 6

    DB:2.61:How Can I Count Unique Text Cells That May Also Contain Blenk Cells 97

    Dear Ron,

    Will you please explain me the calculationsteps of your formula.

    Best Regards,
    Mohammed Nafi.

  • RELEVANCY SCORE 2.61

    DB:2.61:Improve The 'Views' Count In The Message Boards cz



    Currently they refresh every time the page is viewed. When I go to read updates to my post, that is getting counted as an additional view. It would be more useful if the views were a count of unique user views.







    Solved!
    Go to Solution.

    DB:2.61:Improve The 'Views' Count In The Message Boards cz


    Please note I marked this thread with an "Accepted Solution" because this is the best answer we can provide at this time.However, feel free to add additional comments or follow-up questions on this thread.

    Thank you!




    --Give Kudos: found something helpful, important, or cool? Click Kudos Star in a post. --Problem Solved? Click "Accepted Solution" in a post.

  • RELEVANCY SCORE 2.61

    DB:2.61:Count Unique Names Meeting A Condition 9c


    Hi,

    I have a list of names in column 'A', there ae several hundered of them, in column 'B' i have a list of text values, they are 'billable', 'shared contract', 'already paid'. In column 'C' is the manager name.each name in 'A' has one of these text values
    next to them, for example,A B C
    bill billable Jake
    June Shared contractAndrew
    Harry Billable Andrew
    greg BillableJake
    All names occur more than once, and can have the same text value in column B, what i would like is a formula that would return the total number of UNIQUE names in column 'A'based on meeting criteria in B and C, e.g. the name Jake in column 'C' and 'Billable'
    in column B

    Thanks in advance

  • RELEVANCY SCORE 2.61

    DB:2.61:Count Unique Values With Blank Rows 9x


    I have a worksheet that pulls data from a SQL database. In the worksheet I need to count unique values in a column but my data also has blank rows.

    Initially I used the formula " =SUM(IF(FREQUENCY(MATCH(B9:B1396,B9:B1396),MATCH(B9:B1396,B9:B1396))0,1))" when there were no blank values and it worked well.

    But my database has changed now so I need a formula that ignores blanks but counts unique values. The values in my data are a mix of text and numbers. Deleting blank rows wouldn't work since every time I have to use the worksheet I have to refresh the data
    for fresh data from SQL and it would populate the worksheet with the old data with blank rows.

    I have Office 2010 on Win 7. Thanks for any help.

    DB:2.61:Count Unique Values With Blank Rows 9x

    It worked! Thanks.

    Hi,

    Glad I could help and mindful of this thread

    http://answers.microsoft.com/en-us/office/forum/office_2007-excel/mark-as-answer-and-like-competition-among/5a5a20d3-4018-442f-92c3-7a396cea3492

    I'm still going to ask that you mark my response as ANSWER.

  • RELEVANCY SCORE 2.61

    DB:2.61:How To Count Unique Values ? 9c



    Hi All,

    I am new to QlikSense. I need to calculate the unique count of variables in a table/column. For e.g.

    Exit TypeMonthLocationVoluntaryJanuaryPuneInvoluntaryJanuaryPuneVoluntaryFebruary
    Bangalore

    Now I need to calculate = (Total number of Volunatry cases)/(Total number of Volunatry cases+Total number of Involunatry cases)

    Thanks

    Anurag Gupta

    DB:2.61:How To Count Unique Values ? 9c


    Here is also a fantastic post from QlikCommunity that goes over the set analysis syntax.

    Romancing with Set Analysis

  • RELEVANCY SCORE 2.61

    DB:2.61:Compare 2 Columns And Report Gain Or Loss 1z


    Hello!

    I have 2 very large columns of data (about 30k rows each).
    They contain serial numbers from 2 quarters - some are duplicates and some are only in column A (Q1) and some only in column B (Q2).
    I would like to:
    On a separate worksheet show;
    A list of all unique serial numbers in column A and a list of all unique serial numbers in column B with a count of how many serial numbers were in both.

    B1 = TOTAL DUPLICATE serial numbers
    Column A lists serial numbers that were only in Column A on the original sheet - "LOST serial numbers
    Column B lists serial numbers that were only in Column B on the original sheet - "GAIN" serial numbers

    Thanks in advance!

    DB:2.61:Compare 2 Columns And Report Gain Or Loss 1z

    In order to reproduce my solution, just download the example workbook and paste the query in the text frame. After that, click on the command button.
    If you like how it works, you might read more from the subject in:http://www.w3schools.com/sql/default.asp

  • RELEVANCY SCORE 2.61

    DB:2.61:Extract And Compute Unique Entries aj


    Hi,
    In this file link - http://sdrv.ms/1ad1Fcv, I have stated my question in the worksheet. This is what I need to do in Power Query:
    1. First list down all unique entries from column A
    2. Compute the number of unique codes per unique entry listed in 1 above
    3. List unique codes (column wise) per unique entry listed in 1 above
    Can you please help me solve this problem using Power Query.
    Thank you.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    DB:2.61:Extract And Compute Unique Entries aj

    Hi,
    I have solved this problem. Thank you for all your help so far.Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

  • RELEVANCY SCORE 2.60

    DB:2.60:How Can You Count The Number Of Unique Names In An Auto-Filtered Column pc


    I have a excel spreadsheet (Office 2007)that has auto filtered column headings.

    Column B has a list of differentnames that appear numerous times in it.

    I'd like to be able to count the total number of unique names that appear in this column for a given range

    for example B2:B1400

    Is there a formula that can do this as i need to incorporate it into a powershell script

    Here's hoping :)

    DB:2.60:How Can You Count The Number Of Unique Names In An Auto-Filtered Column pc


    Many thanks again Mike,I tried this but it only returns the value of 0.

    Any more suggestions...?

    Hi,

    It works fine for me, did you change the formula at all? Post the formula you used and a small sample of data.

  • RELEVANCY SCORE 2.60

    DB:2.60:Deleting On Compound Key 9x


    Hi!I'm trying to delete duplicate entries in a table that has a 2 column PK. Let's say my table has 3 columns, A, B and C. The PK is on A and B. I also want a unique constraint on A and C, unfortunately I forgot to add that constraint, and in the meantime duplicates were inserted. How would I go about deleting all but 1 of each duplicate. I can SELECT those entries I want deleted with the following query:
    SELECT ToRemove.A, ToRemove.B
    FROM MyTable AS ToRemove INNER JOIN
    (SELECT A, MIN(B) AS ToKeepB, C
    FROM MyTable
    GROUP BY A, C
    HAVING (COUNT(*) 1)) AS ToKeep ON ToKeep.A = ToRemove.A AND ToKeep.ToKeepB ToRemove.B AND ToKeep.C = ToRemove.C
    How do I delete those rows?

    DB:2.60:Deleting On Compound Key 9x

    I'm very curious what the single query would look like Michael.Thanks.

  • RELEVANCY SCORE 2.60

    DB:2.60:Export Unique Entries From One Table To Another mp


    Can someone give me the basic data flow tasks for exporting unique entries from a rawdata table to another 'clean' table?  I need to have the 'clean' table based on unique MAC address entries from the rawdata table.  The entry I would like exported would also be based on the latest ImageDeploydate which is a column in the rawdata table.

    DB:2.60:Export Unique Entries From One Table To Another mp

    Todd:The zip files from above:Read my blog entry for Eliminating Duplicate Primary Keys in SSIS.  You would probably be interested in using the Moderately Difficult technique, using your ImageDeploydate as the scoring column to determine which of the duplicate MAC addresses should be kept.Many Regards,Methody

  • RELEVANCY SCORE 2.60

    DB:2.60:Match Positive Dollar Amounts With Negative, But Dont Double Count cz


    I regularlyhave large sheets with up to 20,000 lines. In one column are all the dollar values.I need to take out all entries that net to zero. The hard part is that there may be 3 $100 entries and 5 -$100 entries so i need to take out 6 of the eight
    entries, leaving 2 -$100 entries. Currently, with abs value I can arrange the numbers so they are next to eachother, but it takes a long time as there are a lot of matches and thousands of line. Can anyone help? Thank you!

  • RELEVANCY SCORE 2.60

    DB:2.60:Count Unique Entries In One Column When They Fall Between Time Frames In Two Other Columns x3


    Hello all,

    I'm working on a report where I'm evaluating production times from a production report I receive daily.

    Column C has the list of producing associates, ColumnGthe production start time, and Column H the production end time (format: H:MM:SS AM/PM). The list of associate names on column C is not unique, meaning that an associate appears more than once.

    What I'm trying to figure out is how many unique associateson column Cproduced between the hours of7 AM and8 AM. For example; below the results show that between 7 AM and 8 AM there are 4 production results, but only Mary and Bill produced during the
    time frame. I would like a count of unique associates between the time frame. In this example, my result would be 2.

    Associate
    Start Time
    End Time

    Mary
    7:13:00 AM
    7:17:00 AM

    Mary
    7:14:00 AM
    7:37:00 AM

    Mary
    7:17:00 AM
    7:51:00 AM

    John
    7:18:00 AM
    8:12:00 AM

    Bill
    7:30:00 AM
    7:45:00 AM

    Bill
    7:30:00 AM
    8:10:00 AM

    Mary
    7:37:00 AM
    8:10:00 AM

    John
    7:37:00 AM
    8:25:00 AM

    Thanks

    DB:2.60:Count Unique Entries In One Column When They Fall Between Time Frames In Two Other Columns x3

    With:
    O3: start time
    P3: end time
    and
    correcting a typo in your posted formula (Shhet vs Sheet), this would be your ARRAY FORMULA:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Sheet1!$C$2:$C$10296,ROW(Sheet1!$C$2:$C$10296)-ROW(Sheet1!$C$2),0,1)),IF((Sheet1!$H$2:$H$10296=P3)*(Sheet1!$G$2:$G$10296=O3),MATCH(Sheet1!$C$2:$C$10296,Sheet1!$C$2:$C$10296,0))),ROW(Sheet1!$C$2:$C$10296)-ROW(Sheet1!$C$2)+1)0,1))

    Using the previously posted sample data, that formula returns for unfiltered data: 2
    When filtered to hide Mary, the formula returns: 1

    Try that same test.
    If the results are correct, the issue probably lies within the data.
    If they are not correct, we need to troubleshoot the problem.

    Let us know what you find.

  • RELEVANCY SCORE 2.59

    DB:2.59:Count Unique Values And Unique Text Comparing Two Columns pj


    I have two colums, one is an ID and the other is a Name. I want to count only the Unique ID and Unique Name, without counting Multiple IDs of the same Name

    Example...

    Column
    A B
    ID Name
    1111 Test
    1111 Test 1
    1111 Data
    1112 Data 1
    1112 Data 1
    1112 Test
    1113 Test
    1113 Test
    1113 Test
    ...

    Count should be 6.... Dont want to count multiple Names or blanks of the same ID

    Thanks so much in advance!!

    DB:2.59:Count Unique Values And Unique Text Comparing Two Columns pj

    Hi,

    May I request you to post your question in the Comments section of this link -

    http://www.ashishmathur.com/count-uniques-with-conditions/#comments

    Thank you.

  • RELEVANCY SCORE 2.59

    DB:2.59:Creating Indexes With A Months_Between Function s1


    I am wondering...can a unique index be created with a "pseudocolumn"?

    I am trying to create one, and am getting a message stating:

    ERROR at line 1:
    ORA-00907: missing right parenthesisbelow here is the attempt/syntax at creating the index:
    -------------------------------------------------------------------------------
    SQL CREATE UNIQUE INDEX loannum_index on SEPTEMBERUPLOAD (LOANNUMBER,DAYLAPSE AS COUNT(MONTHS_BETWE
    EN(REFERRALTOCLTDATE,SYSDATE)));
    CREATE UNIQUE INDEX loannum_index on SEPTEMBERUPLOAD (LOANNUMBER,DAYLAPSE AS COUNT(MONTHS_BETWEEN(RE
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesisI basically have a column that is a loan number, and then a column referencing a date value. The loan number is not to be unique, except that for when the date exceeds a 120 day period. So, I figured I could maybe make pseudocolumn that calculates MONTHS_BETWEEN of the SYSDATE value and the other date column like so:
    SQL SELECT MONTHS_BETWEEN(REFERRALTOCLTDATE, SYSDATE) FROM SEPTEMBERUPLOAD WHERE MONTHS_BETWEEN(REF
    ERRALTOCLTDATE, SYSDATE) -1;

    MONTHS_BETWEEN(REFERRALTOCLTDATE,SYSDATE)
    -----------------------------------------
    -4.8925426
    -4.8602845
    -4.8602845
    -4.8602845
    -4.8602845
    -4.8602845
    -4.8602845
    -4.8602845
    -4.8602845The only problem is, when I try to make this part of an index attribute, that is when I run into trouble. I guess the question is, can this be done? Is my syntax al wrong?

    DB:2.59:Creating Indexes With A Months_Between Function s1

    fair enough shoblock, I'll give it a shot.
    Got XE at home, but only 9i at work.

  • RELEVANCY SCORE 2.59

    DB:2.59:Count Unique Values In A Column Where The Date In Another Column Is In 2010 Or Later. 1j


    I need to count unique values in a column where the date in another column is in 2010 or later.

    For example: Col J has names listed multiple times, Col D has a list of dates.

    D J
    7/12/2009 Sam
    4/15/2010 John
    3/1/2002 John
    11/30/2011 Becky
    2/27/2006 Laura
    1/2/2012 Sam
    8/14/2008 Dennis
    5/24/2011 Sam

    I need it to return the count of unique values, in this case 3.

    DB:2.59:Count Unique Values In A Column Where The Date In Another Column Is In 2010 Or Later. 1j

    If you prefer not to use array formula's the following will work
    In an unused column
    =IF(YEAR(D1)2010,0,1/SUMPRODUCT(--(YEAR($D$1:$D$8)=2010),--($J$1:$J$8=J1)))
    Adjust the range as necessary and then sumthe results from that column offormula's

  • RELEVANCY SCORE 2.59

    DB:2.59:Count Unique Values In A Column ds


    I have a column of city names. I want to create a list of unique city names and a count of that city name. For example:City NameLos AngelesNew York CityLos AngelesLos AngelesSeattleNew York CityMiamiDallasSeattleDallasDallasI would like a count of each unique city name.For example:City NameCountLos Angeles3New York City2Seattle2Miami1Dallas3Ideas?Thanks.

    DB:2.59:Count Unique Values In A Column ds

    Aaron,The following solution finds distinct city names, lists them and counts the frequency of ocurrance.The formula in column B of Data is:=IF(COUNTIF(OFFSET($A$1, 0,0, ROW()-1),A)=0, MAX(OFFSET($A$1, 0, 1, ROW()-1))+1, "")The formula in column A of Summary is:=IF(MAX(Data :: B)=ROW()-1, LOOKUP(ROW()-1, Data :: B, Data :: A), "")And, the formula in column B of Summary is:=COUNTIF(Data :: A, A) if you are in fact interested in the frequency.Hope this does it for you.You asked for unique names, but I believe that you mean to say distinct. The only unique name in your list is Miami, since it's the only one that appears only once, making it unique.Jerry

  • RELEVANCY SCORE 2.59

    DB:2.59:Count Of Unique Records Over Consective Dates fk


    Hello All,

    Need a bit of help, either as a formula or VBA.

    I have a database with 151000 entries of transactional data.

    Column A lists all of the employees by their employee number
    Column B lists all of the report numbers that the employees prepared
    Column C lists all of the dates that the reports were prepared

    All 3 columns contain duplicate data, as the employee can create a report or multiple reports everyday.....and also because more than 1 employee may be involved in 1 report.

    The information I am trying to extract is how many reports are carried over
    consecutive days (namely, how many unique reports took multiple days to complete. *Note* some reports are also updated at a later date, however, I only want a count of the ones that were worked on over CONSECUTIVE dates.

    This biggest problem I am having is how to count the number of reports that are carried over consecutive dates...

    Sample of the data: In this sample I would be looking for all reports that are worked on over consecutive days such as Report# 224119273, which would count as 1.

    Emp ID Report# Date worked
    204898 224038883 7/2/2010
    221377 224038883 7/2/2010
    161592 224119273 5/3/2010
    206935 224119273 5/3/2010
    209186 224119273 5/3/2010
    221988 224119273 5/4/2010
    209186 224119273 5/4/2010
    221988 224119273 5/5/2010
    209186 224119273 5/5/2010
    221988 224119273 5/6/2010
    209186 224119273 5/4/2010
    221988 224119273 5/4/2010

    Any suggestions would be greatly appreciated.

    Christine

    DB:2.59:Count Of Unique Records Over Consective Dates fk

    Hello Teethless mama. I need some urgent help as well. CAn you contact me on my skype as I need to understand what the **** I am doing. Skype name LaetitiavanHeerden - location Alberton South Africa Thank You

  • RELEVANCY SCORE 2.59

    DB:2.59:Unique Column 79


    What is a unique column constraint?

    DB:2.59:Unique Column 79

    also that if you created a primary key column it is also considered unique.

  • RELEVANCY SCORE 2.58

    DB:2.58:Ora-00979 Not A Group By Expression (Within A Join) dj


    Hi all. I've put together the follwing query which works as I expect:

    select substr(msgtext, 2, 12), count(substr(msgtext, 2, 12))
    from archive.kiwi_test, pcs.pix_syslog_id
    WHERE substr(MSGTEXT, 2, 12) = "PIX_SYSLOG_ID"."PIXIDN" (+)
    and to_date(msgdatetime, 'YYYY-MM-DD HH24:MI:SS') SYSDATE - 3600/86400
    group by substr(msgtext, 2, 12)

    The output looks like this:

    Substr(Msgtext,2,12) Count(Substr(Msgtext,2,12))
    PIX-1-105009 225
    PIX-4-400012 25

    The problem I have is when I attempt to introduce the column pix_syslog_id.info to the select statement which contain information about each of the pix_syslog_id.pixidn. The query I attempt looks like this:

    select substr(msgtext, 2, 12), count(substr(msgtext, 2, 12)), info
    from archive.kiwi_test, pcs.pix_syslog_id
    WHERE substr(MSGTEXT, 2, 12) = "PIX_SYSLOG_ID"."PIXIDN" (+)
    and to_date(msgdatetime, 'YYYY-MM-DD HH24:MI:SS') SYSDATE - 3600/86400
    group by substr(msgtext, 2, 12)

    When I try the query I get:

    Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic columns'' checkbox below the region source to proceed without parsing. ORA-00979: not a GROUP BY expression

    Essentially, I want the same output as previously displayed with the description of what each PIX... error code is according to the unique entries (that is, each PIX error code and description is only listed once) in the pix_syslog_id table. It seems like this would be a simple join between two tables so would someone please point out where I'm going wrong?

    Thanks! Len

  • RELEVANCY SCORE 2.58

    DB:2.58: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.58

    DB:2.58:Refining Sql Replacement For Oracles Wm_Concat Function (Pivoting Rows To Column) xa


    Greetings,
    I am moving a query from Oracle SQL to Microsoft SQL and am having some difficulty with the transition. Part of my code (listed here) used to pivot the data and display RESULT_VALUE
    by the CPI_SEQ . In Oracle this was easy to do by using the function: WM_CONCAT.
    I have been ‘Googling’ for the non-Oracle way to achieve this and stumbled on to the “STUFF((SELECT…)”
    method. This almost works for me except that it places everything into one cell, whereas
    I need to concatenate RESULT_VALUE by CPI_SEQ (the CPI_SEQ is the unique ID). The current code gives me this:
    RESULTS
    Anxiety, Depression, Diabetes,
    COPD, ARDS

    Whereas I want my code to present it this way instead
    CPI_SEQ
    RESULTS
    22
    Anxiety, Depression
    44
    Diabetes
    46
    COPD, ARDS

    SELECT (STUFF((SELECT ',' RESULT_VALUE
    FROM
    (
    SELECT DISTINCT
    C1.CPI_Seq AS CPI_SEQ,C1.Result_Value AS RESULT_VALUE
    ,count(FCurrent.Field_Name) Current
    ,count(FPast.Field_Name) Past
    ,Count(*) Count
    ,CASE
    When count(FCurrent.Field_Name) 0 and count(FPast.Field_Name) 0 and count(Fcurrent.Field_Name) count(FPast.Field_Name) = Count(C1.PF_RESULT_SEQ) Then CONCAT(C1.Result_Value,'(C/P)')
    When count(FCurrent.Field_Name) 0 and count(FPast.Field_Name) =0 and count(Fcurrent.Field_Name) count(FPast.Field_Name) = Count(C1.PF_RESULT_SEQ) Then CONCAT (C1.Result_Value,'(C)')
    When count(FCurrent.Field_Name) = 0 and count(FPast.Field_Name) 0 and count(Fcurrent.Field_Name) count(FPast.Field_Name) = Count(C1.PF_RESULT_SEQ) Then CONCAT(C1.Result_Value,'(P)')
    End Result
    From
    [AnalyticsDW].[dbo].[rr_stag_PF_Results] A1
    join [AnalyticsDW].[dbo].[rr_stag_PF_Results] C1 on A1.PF_Result_Seq = C1.PF_Result_Seq
    join [AnalyticsDW].[dbo].[rr_PF_Fields_Dept] FCurrent on A1.PF_Result_Seq = FCurrent.RES_SEQ
    AND FCurrent.Field_Name = 'Current'
    AND A1.Label_Seq in('187582', '187576','187612','187600','187618','187612')

    left join [AnalyticsDW].[dbo].rr_PF_Fields_Dept FPast on A1.PF_Result_Seq = FPast.Res_Seq
    AND FPast.Field_Name = 'Past'
    AND A1.Label_Seq in('187583', '187577','187613','187601','187619','187613')

    WHERE
    A1.Result_Value ='Yes'
    AND (C1.Result_Value in ('Dyspnea', 'Confusion','Pressure Ulcer', 'Stasis Ulcer','Depression','Anxiety'))
    GROUP BY
    C1.CPI_Seq,
    C1.Result_Value
    ) L1
    FOR XML PATH('')),1,2,'')) RESULTS

  • RELEVANCY SCORE 2.58

    DB:2.58:Qvd - Key Best Practices ac



    I am quite new to Qlikveiw coding and want to better understand how to build the most efficient QVDs possible.

    Am I correct in assuming that any compression benefit would be lost if I make a truly unique column? It seems in testing that when I only include data/date/ID columns I get a great compression rate. When I include an integer ID column I seem to get ok compression. But now that I have created a few fairly unique %Key columns I am basically getting horrible compression.

    And on the subject of Keys. I have a fairly unique data set in that it is occurances of compter names over time (maybe millions of events). I want to have the ability to count them various ways. Distinct machines over a week, a month, a day, etc. I frequently count and group by month but occasionally do need the data by day. When I create my unique keys to use in my link tables - should I be creating 2 keys, one for the daily models and one for the monthly models? Or would the daily key be enough if I also include in the data set a new column for 'StartOfMonth' or something?

    DB:2.58:Qvd - Key Best Practices ac


    Hi,

    Please read this thread for some valuable information on best practices.

    Thanks,

    DV

  • RELEVANCY SCORE 2.58

    DB:2.58:Duplicate Entries In Apex Report f8


    Hello everyone,

    I'm generating an APEX report that selects records and the user assigned to them. The records are all unique in the main table, but I have to link 4 different tables together to get all the information I need. In one of the tables, there are many different entries referencing the same CallID. This is causing my report to display all of those entries, even though they are duplicates on the report. Is there a way to make that column in the table unique, or prevent the entries from appearing multiple times?

    I've tried using the Group By statement, but I get this error "ORA-00979: not a GROUP BY expression".

    Thanks.

    DB:2.58:Duplicate Entries In Apex Report f8

    Hi,

    It would depend on what data you actually want to end up with. If the information in each record on the final table is unique apart from the CallID value, then you will end up with "duplicates" of the data in the other three tables. If you think of a simplified version of this, you have a parent/child relationship between the tables - looking at things from the child table's point of view, several child records may have the same parent. Therefore, if you create a report that includes ALL details from ALL tables, then you will see the parent's information displayed for each child.

    So, if you only want to see one parent's information regardless of the number of child records you have, you either have to combine the child records into a single value or use the Apex report's "Break Formatting" functionality (shown at the bottom of the report's Report Attributes tab). Using the GROUP BY within the SQL statement would normally require you to use an aggregation function to return a single value from the child table - for example, MAX(), MIN(), SUM(), COUNT() etc. If you didn't want to aggregate data, you could use DISTINCT instead (as in SELECT DISTINCT fieldlist FROM tables) but this will only remove duplicate records where ALL values in ALL columns matched.

    So, it does depend on the data itself and how you want to display it.

    Andy

  • RELEVANCY SCORE 2.58

    DB:2.58:Count Unique Names In A Pivot Table. d7



    hi,
    okay so i have a table where on column A i have a list of projects and on column B i have a list of countries (some countries appear more than twice.) I am trying to create a pivot table where i will get a count of "unique" countries. Is there any way i
    can do that? I know i could use countif but i have to use a pivot table (i have more data included which i exclude from this question)

    Thanks!

    DB:2.58:Count Unique Names In A Pivot Table. d7


    as I said:
    =SUM(1/COUNTIF(Country,Country))
    array-entered with Ctrl+Shift+Enter
    where Country is the (preferably named) range containing the countries in the original list.

    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    thnx man it works like a charm

  • RELEVANCY SCORE 2.57

    DB:2.57:How To Make A Sharepoint List Column To Hold Unqiue Values x7


     
    Hi,
        I would like to know how to make a sharepoint list column to hold unique values. In other words, the column should not take dupliacte entries.
     

    DB:2.57:How To Make A Sharepoint List Column To Hold Unqiue Values x7

    Another approach is to write an event handler and attach it to the ItemAdding event of the list. In your code, you can either cancel the list item addition, or you could change the value in the desired column to make it unique.
     
    Hope this helps.
     
    Matt Morse
    https://blogs.pointbridge.com/blogs/morse_matt
     

  • RELEVANCY SCORE 2.57

    DB:2.57:Sequential Count Of Rows In A Column 7c



    Hi,

    In a query, I have a table defined with the relevant detail required by the business. I need to insert a count into the first column so it identifies a unique number. e.g. row 1 = 1, row 2 = 2 etc.

    How can this be done?

  • RELEVANCY SCORE 2.57

    DB:2.57:Adding Additional Column With Reference To Existing Column Based On Unique Count 1j


    Dear all
    myrequirementis creating a new column in excel with reference to existing column
    purpose of new column is to count unique ids in existing column
    i need to write logical function in excel if id occurs once then new column value should be 1
    if duplicate id occurs them it should be represented by 0 in new column
    can any one help me in writing the function......

    DB:2.57:Adding Additional Column With Reference To Existing Column Based On Unique Count 1j

    Jon:
    Your second formula works like a charm... after I figured out it was not an array formula.
    Regards,Rich Locus, Logicwurks, LLC
    http://www.logicwurks.com

  • RELEVANCY SCORE 2.57

    DB:2.57:Alv: Count Of Unique Entries s3



    Hi Team,

    A conventional report has Plant, Delivery Doc. No., Customer ID and Material # in its line, sorted by Plant.

    On change of the Plant, the count of unique Delivery documents,Customers and Materials is displayed as shown below,

    Plnt Delivery# Ord/PO# SaTy Cust

    3100 81546208 800865 KB 11316

    3100 81546205 800864 OR 730

    3100 81546206 800864 OR 730

    3100 81546209 800865 KB 11316

    3100 81546206 800864 OR 730

    3100 81546207 800864 OR 730

    3100 5 2 2

    I require to display this report using ALV, how to display this unique entries count in ALV.

    Thanks Regards,

    Lakshmanan

    DB:2.57:Alv: Count Of Unique Entries s3


    Hi

    As on chnage of is obselete in ECC 6.0.

    use at-new and at-end control break statements on plant.

    Regards

  • RELEVANCY SCORE 2.57

    DB:2.57:Counting Unique Items With Two Variables pd


    Count a unique set of values based on two variables.

    For example – how many unique product IDs are associated with customer x and city
    Column A = Product IDs
    Column B = Customers
    Column C = City Codes

    Thanks for your help in advance.

    DB:2.57:Counting Unique Items With Two Variables pd

    Here's a small sample file that demonstrates this.
    zPinto1759.xlsx10kb
    http://cjoint.com/?0lBxb4IAGmv
    That link will expire in 21 days.
    --
    Biff
    Microsoft Excel MVP

  • RELEVANCY SCORE 2.56

    DB:2.56:Updateexception Because Of Unique Key 9p


    In my entity model I have an entity class for a table with a (mult-column) unique key. When changing the values of two entries, I get an exception,about aunique key constraintviolation. theconstraint is not violated, when all changes
    are commited.
    Is there a way to walk around this problem?

    DB:2.56:Updateexception Because Of Unique Key 9p

    Hi,
    Even during the transaction, the unique key constraint needs to be enforced else identifying a row is not possible ie if you have rows 1 and 2 then :
    update MyTable SET pk=2 WHERE pk=1 -- Violates constraints
    update MyTable SET pk=1 WHERE pk=2 -- If it doesn't would change both rows anyway

    update

    t set a=case
    when a=1
    then 2
    else 1
    enddoes work as the update is done in one
    go.
    For now I don't see an easy workaround. If this swap is triggered by some rule you could perhaps explicitely swap by using an intermediate dummy key so that you never have a collision to do something such (pseudo
    code):
    o1.unique=Int.MinValue // Never used elsewhere
    o2.unique=1 // Was 2
    SaveChanges
    o1.unique=2 // Was 1 at the beginning...
    Savechanges
    Perhaps knowing what kind of data needs to be swapped this way would trigger some suggestion to handle this (the fist one coming to my mindbeing to swap the other columns rather than those uniquely defined
    columns but it could ahve other unwanted effects depending on your schema).

    SubmitChanges

    Please always mark whatever response solved your issue so that the thread is properly marked as Answered.

  • RELEVANCY SCORE 2.56

    DB:2.56:How Do I Represent Select Count Distinct In Qlikview? sm



    Hi

    How do I write the equivalent of SELECT COUNT(DISTINCT TestIDs) in QlikView?

    I would like to bring in the count of unique numbers in a column from a table and represent it on a chart. I wrote Count(TestIDs), but the numbers are different from when I run same in SQL. Also, I can't seem to work out the formula in Qlikview that equates to SELECT COUNT(Distinct TestIDs).

    Can anyone please help please.

    Thanks

    DB:2.56:How Do I Represent Select Count Distinct In Qlikview? sm


    I suspect you want TestIDs as first dimension, and as expression you should probably use Count(TestIDs) or count of some other field. Count will count the number of records where this field is not NULL.

    The field "flag" is something that you can display as list box next to the chart. Then you can make your selection (filter) in the "flag" field and see the result in the chart.

    An alternative is to use Count(if(Flag='Yes',TestIDs)), but this construction is slow when you have large amounts of data.

  • RELEVANCY SCORE 2.56

    DB:2.56:Problem Inserting Records From Staging Table To Actual Table a9


    Hi all,

    I have 2 tables

    SAMPLE - ACTUAL RANGE-HASH PARTITIONED TABLE WITH UNIQUE INDEX ON COL1,COL2
    SAMPLE1 - STAGING TABLE TO LOAD WEEKLY DATA WITH SAME SET OF COLUMNS AS SAMPLE

    I am facing problem in inserting records to actual table even after removing duplicate entries from staging table.

    SELECT COUNT(*) FROM SAMPLE1 WHERE COL1 IN (SELECT COL1 FROM SAMPLE); -- 1047048
    DELETE FROM SAMPLE1 WHERE COL1 IN (SELECT COL1 FROM SAMPLE); -- 1047048
    COMMIT;
    INSERT INTO SAMPLE SELECT * FROM SAMPLE1;
    QL Error: ORA-00001: unique constraint (SCHEMA1.UQ_SAMPLE) violated
    00001. 00000 - "unique constraint (%s.%s) violated"
    SELECT COUNT(*) FROM SAMPLE1 WHERE COL1 IN (SELECT COL1 FROM SAMPLE); -- 0
    SELECT COUNT(*) FROM SAMPLE1 WHERE EXISTS (SELECT S1.COL1 FROM SAMPLE1 S1,SAMPLE S WHERE S1.COL1=S.COL1); -- 0

    MERGE INTO SAMPLE S
    USING
    ( SELECT * FROM SAMPLE1) S1
    ON (S.COL1 = S1.COL1 AND S.COL2=S1.COL2) WHEN NOT MATCHED THEN INSERT
    (COL1,COL2,COL3) VALUES
    (COL1,COL2,COL3);
    QL Error: ORA-00001: unique constraint (SCHEMA1.UQ_SAMPLE) violated
    00001. 00000 - "unique constraint (%s.%s) violated"Should I use anything font color="red"
    other than "IN" and "EXISTS" /font
    to cross check the records?

    Regards,
    Sam.

  • RELEVANCY SCORE 2.56

    DB:2.56:Unique Record Selection 1j



    Hi,

    We will be entering CR no's in one Cell of excell sheet with Comma Separation. My requirement is to count total no of such entries and count unique instances of the CR in that given list

    For example I will have any entry as 162345,172387,185423, 162345.. In this case total count will be 4 and 3 are Unique. Also we may have sum up this across 3 Cells also

    Can QlikView Support this request..

    Thanks,Soma

    DB:2.56:Unique Record Selection 1j


    Hi,

    There is a function in scripting called 'SubField' which can be used to break a string which has delimited text.

    Simply use this code to create a new field and then apply count on this field to get desired result:

    T:LoadSubField(CR_NO,',') as CRFrom xyz;

  • RELEVANCY SCORE 2.56

    DB:2.56:Unique Armor Piece fc


    When will they bring back the unique armor piece in Recon team?



    LegoMonkeeMen

    DB:2.56:Unique Armor Piece fc

    lancerrex8 wrote: Siah13 wrote: LegoMonkeeMen wrote:When will they bring back the unique armor piece in Recon team?br/You can still buy itbr/It says unique armour piece is no longer avaliable for purchase every time you try and buy a hero with it. The worst part is that it was out of order before the 3.0 elements came out!!!!!! br/Will any body reply to this post?Yikes. Even through it sitll says "Add to cart"?



    Don't ask why my Mom hates Chima, Guardians of the Galaxy and Max Rebo form Star Wars

  • RELEVANCY SCORE 2.56

    DB:2.56:Count Entries In A Pivot Table 37


    My data table has a column for "last name" and a column for "city." I created a pivot table with "city" in the row lables (giving me only the unique city names) and "city" in the value field so that I could get a count of each unique city name. If the
    city name is blank in the data table, I get "blank" listed as a row lable in the pivot table, but I do not get a count. There is nocount of records where the city has been left blank.
    However, if Iput "city" in the row labels and "name" in the value field, then I get a count of records with the city field blank, but the column title is "count of last name" and I want it to read "count of city."
    Is there a way to get a number to show up next to "blank" if I have "city" in the value field?

  • RELEVANCY SCORE 2.56

    DB:2.56:Drag And Drop Question - Limit To Number Of Entries az


    looks like the drag and drop question slide has a limit to
    the number of entries in column 1.
    8 entries to be precise.
    That's ok for me in this case, as my example has 8 entries
    for column 1. However when you run the application, it seems it
    will only shows 6 entries in column 1. I tried to look for the
    missing 2, hoping they might be hidden or slightly off screen
    somewhere. Has anyone encountered this problem?

    DB:2.56:Drag And Drop Question - Limit To Number Of Entries az

    This is interesting. As well as having the above problem, I
    was getting an incorrect result when dragging to the correct points
    in column 2. even though it should be correct
    Because of the issue in the previous post, i.e. not being
    able to see the last two entries, I decided to delete them.
    Hey presto this seemed to fix this above problem. Now my drag
    and drop works.
    It seems captivate doesn't like to go beyond 6 entries in
    column 1 i.e. doesn't show the extra entries and also causes an
    false error when successfully completing the drag and drop

  • RELEVANCY SCORE 2.56

    DB:2.56:Unique Text In Column With Specific Criteria In Multiple Columns. jz


    I am attempting to get a count of unique items in a list of items which begin with a specific text xxx that also has a date in another column which is = today().

    DB:2.56:Unique Text In Column With Specific Criteria In Multiple Columns. jz

    Excel 2010, Table, PivotTable
    http://58918aa17d4002474361-7ac2b7ee2bc9d74042ff2920135ae995.r25.cf1.rackcdn.com/03_09_13.xlsx
    If you get *.zip, don't unzip, just rename *.xlsx

  • RELEVANCY SCORE 2.56

    DB:2.56:Macro To Count Rows 11


    Hi. I need a macro code to count down column AQ, then in column AS enter a count. So assuming there are 250 entries in column AQ, starting from AQ1 down to AQ250, then in column AS, starting at AS1 i want a count. The amount of entries in column Q can change
    a lot which is why i wanted the automatic quote.

    Thanks

  • RELEVANCY SCORE 2.56

    DB:2.56:Counting Only Once If The Entry Appears Multiple Times kc


    Hi,
     
    Here's the situation.  I have a column in an Excel 2003 document with multiple number entries.  Each number entry appears more than once.  I want to count the number of unique entries in this column, meaning that if an entry appears more than once in the column, I only want to count it once.  Does anyone know how to do this because I really have no idea.
     
    Thanks.

  • RELEVANCY SCORE 2.56

    DB:2.56:Need Help With Query To Filter Duplicates Based On Custom Logic kd


    Following is my table structure:

    CREATE TABLE [Fusion].[flt_IQConnector](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Unique_Number_26] [varchar](8000) NULL,
    [PVP_Name_26] [varchar](8000) NULL,
    [PVP_Tag_26] [varchar](8000) NULL,
    [Default_Scaling_26] [varchar](8000) NULL,
    [Default_Offset_26] [varchar](8000) NULL,
    [Is_Active_26] [varchar](8000) NULL,
    [Mark_for_Deletion_26] [varchar](8000) NULL,
    [ValidationMessage] [varchar](8000) NULL,
    [ProcessParameter_Id] [int] NULL,
    CONSTRAINT [PK_flt_IQConnector] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =

    ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY]

    Below is sample data:

    ID
    Unique Number
    ProcessParameter_Id
    PVP Name
    PVP Tag
    Default Scaling
    Default Offset
    Filter
    Is Active
    Mark for Deletion
    ValidationMessage

    1
    9999
    269
    Box Serial Number
    RapidPak1 Monitor1
    1
    0

    TRUE

    2
    9999
    269
    Box Serial Number
    Oven Wt Loss
    1
    0

    TRUE

    3
    10000
    269
    Box Serial Number
    Oven Wt Loss
    1
    0

    TRUE

    4
    10000
    269
    Box Serial Number
    Oven Wt Loss
    1
    0

    TRUE

    Below is the query I am using to validate for duplicate rows. We are assuming that the unique key to filter the duplicates

    here is Unique Number ProcessParameter_Id. We only consider those rows duplicate that have the same unique keys but

    different data in any other column. So, In sample data above, the rows for 'Unique Number' =9999 and 'ProcessParameter_Id' = 269 should be considered duplicate as they have different data in 'PVP Tag' column. While the rows for 'Unique Number' =10000
    and 'ProcessParameter_Id' = 269 should not be considered duplicate as they have same data in 'PVP Tag' column. I need to put a message in validationMessage column if rows are duplicate. This query seems to be working file elsewhere in database but not for
    this table. any suggestions are greatly appreciated..

    UPDATE fusion.flt_iqconnector
    SET validationmessage = CASE
    WHEN Len(Coalesce(validationmessage, )) 0 THEN
    validationmessage + |
    ELSE
    END +
    Duplicate rows are not allowed for this combination: (Unique Number, ProcessParameter_Id) should be unique
    FROM fusion.flt_iqconnector ft1
    INNER JOIN(SELECT unique_number_26,
    processparameter_id
    FROM (SELECT ft1.unique_number_26,ft1.processparameter_id,ft1.[Default_Scaling_26],ft1.[Default_Offset_26],ft1.[Filter_26],
    ft1.[Is_Active_26],ft1.[Mark_for_Deletion_26]
    FROM fusion.flt_iqconnector ft1
    INNER JOIN(SELECT unique_number_26,processparameter_id
    FROM fusion.flt_iqconnector
    WHERE Len(Coalesce(validationmessage,)) = 0
    GROUP BY unique_number_26,processparameter_id) ft2
    ON ft1.unique_number_26 = ft2.unique_number_26
    AND ft1.processparameter_id = ft2.processparameter_id
    GROUP BY ft1.unique_number_26,ft1.processparameter_id,ft1.[Default_Scaling_26],ft1.[Default_Offset_26],ft1.[Filter_26],
    ft1.[Is_Active_26],ft1.[Mark_for_Deletion_26]) ft12
    GROUP BY unique_number_26,processparameter_id
    HAVING COUNT(*) 1) ft2
    ON ft1.unique_number_26 = ft2.unique_number_26
    AND ft1.processparameter_id = ft2.processparameter_id

    DB:2.56:Need Help With Query To Filter Duplicates Based On Custom Logic kd

    A lot of the query you posted is redundant and unnecessary. Any reason why you have to use the other code? Are you using SQL 2000? The code I posted will be much better performing and easier to maintain.

    UPDATE f
    SET ValidationMessage =
    CASE
    WHEN Len(Coalesce(validationmessage, )) 0
    THEN validationmessage + |
    ELSE
    END +Duplicate rows are not allowed for this combination: (Unique Number, ProcessParameter_Id) should be unique
    FROM(
    select [Unique_Number_26],[ProcessParameter_Id],
    DENSE_RANK() over(partition by [Unique_Number_26],[ProcessParameter_Id] order by [PVP_Name_26],[PVP_Tag_26]) as seq
    from @flt_IQConnector
    ) AS t
    inner join @flt_IQConnector f
    on f.Unique_Number_26 = t.Unique_Number_26 and f.ProcessParameter_Id = t.ProcessParameter_Id
    where
    t.seq = 2
    AND (f.ValidationMessage IS NULL OR f.ValidationMessage NOT LIKE _%)

  • RELEVANCY SCORE 2.55

    DB:2.55:Count Subtotal Unique Items In A Column zc


    I have a column in Excel that I would like to not only count the number of unique names in, but I would also like to SUBTOTAL based on filtered data.

  • RELEVANCY SCORE 2.55

    DB:2.55:Count Of Unique Values From One Of The Column Of Datagrid 7f


    Hi Pals,
    I want to find the count of unique values from a datagridview, is this possible in VB.Net ?
    See below example;

    Now I want to get the unique values from Priority/Status column with their names and count.
    Any help would be greatly appreciated.
    Thanks,
    Srini

    DB:2.55:Count Of Unique Values From One Of The Column Of Datagrid 7f

    Excellent.... This is what I was asking for....
    Thanks you
    Kevininstructor

    Kevininstructor

    MCC