Count distinct values from 2 different collumns

[#1]

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

[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.

[#3]

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]

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