Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

DAX conversion to Qlik Sense - duplicate row count

Hi,

 

 

 

I am just a week old in Qliksense, however coded similar DAX in power BI. Need help to convert it Qlik Sense.

Below DAX i wrote to get duplicate file count based on there document title.  Reviewid is autonumber (index) in the table,  doctitle is document name. If there are multiple duplicate doctitle then below DAX add one more column and keep on adding 1 in each row if duplicate doctitle found. 

 

Duplicate Doc # =
VAR number =
calculate(COUNTROWS('docreview reviews'),
FILTER('docreview reviews',
'docreview reviews'[doctitle] = EARLIER('docreview reviews'[doctitle])
&& 'docreview reviews'[reviewid] < EARLIER('docreview reviews'[reviewid])))

RETURN IF(ISBLANK(number),1,number +1)
 
Output is below which i need in Qlik Sense too: please help
 
ReviewiddoctitleDuplicate Doc #
1abcde5
2abcde4
3abcde3
4abcde2
5abcde1
6xyz2
7xyz1
Labels (1)
1 Solution

Accepted Solutions

Hi, you can use: If(Below(TOTAL doctitle)=doctitle, Below(TOTAL Duplicate)+1, 1)

But selectons can change that numbers, if you want fixed numbers to count duplicates you can do it in script, something like:

Data:
LOAD 
	Reviewid,
	doctitle,
	If(doctitle=Peek(doctitle), Peek(Duplicate)+1, 1) as Duplicate
Resident
	OrigData // Table name with data
Order By
	doctitle, Reviewid desc
;
DROp Table OrigData;

This can also help with intercalated doctitles.

View solution in original post

2 Replies

Hi, you can use: If(Below(TOTAL doctitle)=doctitle, Below(TOTAL Duplicate)+1, 1)

But selectons can change that numbers, if you want fixed numbers to count duplicates you can do it in script, something like:

Data:
LOAD 
	Reviewid,
	doctitle,
	If(doctitle=Peek(doctitle), Peek(Duplicate)+1, 1) as Duplicate
Resident
	OrigData // Table name with data
Order By
	doctitle, Reviewid desc
;
DROp Table OrigData;

This can also help with intercalated doctitles.

View solution in original post

Contributor II
Contributor II

Thank you very much, it worked well.