Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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