Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

find records with matching dates in a date field to compare

Hello,

I am quite new to Qlik and I am trying to create a set expression where I can actually retrieve only the records which have matching processing dates  for example

 

BillID  Name      ProcessingDate Amount     VOIDSTATUS

1          Rick           1/1/2020                       25$            N

2           Rick          1/1/2020                      - 25$          Y

3           Rick          1/4/2020                       5$              N

4           Rick          1/14/2020                       45$          N

 

So I need Rick whose processing date matches, Bill ID's 1 and 2. So that I can compare those records to see what happened with the amount

Something like 

BillID  Name      ProcessingDate           Amount        VOIDSTATUS

    2           Rick           1/1/2020                          25$                            Y

 

 

I do not if this is possible, but any input will be really helpful. Please let me know if this is clear 

 

 

Labels (2)
5 Replies
Highlighted
Specialist III
Specialist III

Re: find records with matching dates in a date field to compare

attached a version

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Contributor II
Contributor II

Re: find records with matching dates in a date field to compare

Thanks, Taoufiq, I am still learning I could not access the qvf file as I am using Qliksense not Qlikview and do not have qliksense desktop. Would you be able to show me a screenshot send qliksense file please? 

Highlighted
Specialist III
Specialist III

Re: find records with matching dates in a date field to compare

ok on Script :

Data:

load * inline [
BillID,Name,ProcessingDate,Amount,VOIDSTATUS
1,Rick,1/1/2020,25,N
2,Rick,1/1/2020,-25,Y
3,Rick,1/4/2020,5,N
4,Rick,1/14/2020,45,N
];

output:

load ProcessingDate as ProcessingDate,
	count(ProcessingDate) as countflag

resident Data
group by ProcessingDate
;
join load * resident Data;

drop table Data;

 

then add table

Dimension :BILID,Name,ProcessingDate,voidstatus

Mersure :

=sum({<countflag={">1"}>} Amount)
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
MVP
MVP

Re: find records with matching dates in a date field to compare

You can create a simple set analysis expression. Create chart with Dimension BillID, ProcessingDate,ValidationStatus and below expression

=sum({<ProcessingDate=p({1}ProcessingDate)>}Amount)

Highlighted
Contributor II
Contributor II

Re: find records with matching dates in a date field to compare

Thanks, A lot for your response.