Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
olenedderhoff
Contributor II
Contributor II

Record Filters like Excel records macros

 

I have a table that shows me duplicate customers, including the date each of the duplicate entries were made. 

So for example the Customer with the ID XYZ123 was created on 1-1-24 and due to a user error he was created again on 2-16-24 with a different ID, but same name, address, phone number etc. .

Now I filter this table for all of the duplicates created in the last 30 days, so in my example only the one entry of the customer will be shown in the table.

However, want both entries shown. I can do this with two extra clicks, first filtering for the name of the customer and than removing the filter for the date.

Is there a way to record this 3-Click process and repeat it on the push of one button (like I can record a Macro in Excel)? And if not, can anyone think of another option to achieve my goal?

Labels (1)
3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can replicate this "select possible" scenario using the P() set function. Something like:

Count({<Customer=P({1<Age={"<31"}>} Customer)>} Customer)

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn...

-Rob

olenedderhoff
Contributor II
Contributor II
Author

This works when I want to count all of the possible IDs like this:
=Count({<ID = P({<DaysSince={">=-30"}, [Flag_Customer_Duplicate]={1}>} ID)>} DISTINCT ID)

But when i try to input the formula without the count function in front into a Selection Formula like this
={<ID = P({<DaysSince={">=-30"}, [Flag_Customer_Duplicate]={1}>} ID)>} DISTINCT ID 
I get: Garbage after expression: 'ID'

How do I use P() specifically to select all of the values in the field ID, that satisfy {<DaysSince={">=-30"}, [Flag_Customer_Duplicate]={1}>}?

 

marcus_sommer

I think it might be more suitable to create the appropriate information within the data-model, for example by adding recno/rowno to the load and/or to combine some of your address-fields as a large string or as hash/autonumber and then applying a count-aggregation and/or using interrecord-functions like previous/peek within a sorted resident-load to flag the records and/or calculating the date-offset of the duplicates or similar stuff.