topic Count distinct values from 2 different collumns in QlikView

Count distinct values from 2 different collumns

Mon, 25 Apr 2011 14:45:10 GMT

Dears,

how can I count values from 2 different collumns?

How can I do it?

Thanks

Count distinct values from 2 different collumns

Miguel_Angel_Baeyens — Mon, 25 Apr 2011 15:00:13 GMT

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.

Count distinct values from 2 different collumns

Tue, 26 Apr 2011 18:39:07 GMT

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

Count distinct values from 2 different collumns

johnw — Wed, 27 Apr 2011 02:23:58 GMT

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