Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count distinct values from 2 different collumns

Dears,

how can I count values from 2 different collumns?

Example:
Collumn A | Collumn B
Company 1 | A
Company 2 | B
Company 3 | A
Company 1 | A
Company 2 | B
Company 2 | A

What I want is to count:
Company 1 A appears 2 times
Company 2 A appears 1 time
Company 2 B appears 2 times
Company 3 A appears 1 time

How can I do it?

Thanks

3 Replies
Miguel_Angel_Baeyens

Hello,

If you search the forums you will find a lot of discussions about this, but this code may help you

Data:LOAD "Column A", "Column B", "Column A" & '/' & "Column B" AS KeyToCount INLINE [Column A, Column BCompany 1, ACompany 2, BCompany 3, ACompany 1, ACompany 2, BCompany 2, A]; Step1:NOCONCATENATE LOAD *RESIDENT DataORDER BY KeyToCount; DROP TABLE Data; Step2:LOAD *, If(KeyToCount = Previous(KeyToCount), RangeSum(Peek('KeyCount'), 1), 1) AS KeyCountRESIDENT Step1; DROP TABLE Step1;


Since I'm loading from INLINE tables I need to do some steps you can save if you sort in your SQL query when loading into memory.

Hope that helps.

EDIT: Depending on your data, accumulating in the script will save rendering time in your charts. According to this script, you can easily now create a chart using KeyToCount as dimension and Max(KeyCount) as expression to get the results you expect.

Not applicable
Author

Other choice based in Miguel example is:


Data:
LOAD "Column A" as ColumnA, "Column B" as ColumnB, "Column A" & '/' & "Column B" AS KeyToCount INLINE [
Column A, Column B
Company 1, A
Company 2, B
Company 3, A
Company 1, A
Company 2, B
Company 2, A
];

Step1:
LOAD ColumnA,
ColumnB,
count(KeyToCount) as KeyCount
RESIDENT Data
group by ColumnA, ColumnB;

DROP TABLE Data;


Regards

johnw
Champion III
Champion III

If you aren't trying to actually change the table structure, and just want the count in a chart, something like this:

Dimension 1 = Collumn A
Dimension 2 = Collumn B
Expression = count("Collumn A") // or an ID, or whatever