Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

Need unique count baised on unique number

Hi All,

I have a table like below. "Decp1" is repeated 5 time, but I want unique count on its "Number". If I remove duplicates in number then I get 3 & I need this as a flag in script but not 5.

NumberTextDup
1003Decp13
1003Decp13
1003Decp92
1004Decp13
1004Decp13
1005Decp13
1005Decp70
1005Decp80
1005Decp92
Labels (1)
3 Replies
Channa
Specialist III
Specialist III

make resident load Max(dup) group by 

Channa
felipe_dutra
Partner - Creator
Partner - Creator

There are many ways to do this. You can do an autonumber of the two fields, concatenated, or you can perform the distinct sum of the fields, concatenated.

Examples:

Tab:
LOAD *, AutoNumber(Number&'|'&Text) as Dist Inline
[
Number,Text
1003,Decp1
1003,Decp1
1003,Decp9
1004,Decp1
1004,Decp1
1005,Decp1
1005,Decp7
1005,Decp8
1005,Decp9
];

Tab:
LOAD  distinct * Inline
[
Number,Text
1003,Decp1
1003,Decp1
1003,Decp9
1004,Decp1
1004,Decp1
1005,Decp1
1005,Decp7
1005,Decp8
1005,Decp9
];

Count(Distinct Number&'|'&Text)

HirisH_V7
Master
Master

Hi Check out using this code below,

Data:
LOAD  * ,
Count( Distinct Number&'|'&Text) as Temp_Count
 INLINE [
    Number, Text, Dup
    1003, Decp1, 3
    1003, Decp1, 3
    1003, Decp9, 2
    1004, Decp1, 3
    1004, Decp1, 3
    1005, Decp1, 3
    1005, Decp7, 0
    1005, Decp8, 0
    1005, Decp9, 2
]Group By Number,Text,Dup;
 join
 load Text,
Count(Temp_Count) as Final_Count
Resident Data Group by Text ;
Drop Field Temp_Count;

PFA For ref.

HirisH
“Aspire to Inspire before we Expire!”