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: 
Not applicable

Filter Date Between Two Uncoupled Tables

Hi,

I have two tables that I'm trying to link by date. Each table has a date field, but the fields are named differently to avoid circular references. Is it possible via set analysis to make the two date fields equal each other? I've tried to do this so far unsuccessfully.

The fields I'm trying to link (Calendar Date and Registration Date) are in the tables below:

error loading image

I've been trying to use the following set analysis to link the Registration and Calendar Date fields:

count({<[Calendar Date] = [Registration Date]>} [Hit Time])

However, all rows are coming back with a 0 value. For some reason it's unable to match the dates, but when I remove the set analysis it shows 1986. So the data's there, Qlikview's just unable to filter it properly.

The table below shows the output without set analysis (1986) and with it (0).

error loading image

Any insight into how to link these date fields with set analysis will be greatly appreciated.

Best,

Matt

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

I am not a set analysis expert but I suspect that might be the issue.

Try something like:

count(IF([Calendar Date] = [Registration Date], [Hit Time]))

View solution in original post

5 Replies
Anonymous
Not applicable
Author

Are your Calendar and Hit Date in the same date format?

Not applicable
Author

Yes, the date formats are identical. Here's a screenshot, perhaps you can see something I'm missing:

Best,

Matt

Anonymous
Not applicable
Author

I am not a set analysis expert but I suspect that might be the issue.

Try something like:

count(IF([Calendar Date] = [Registration Date], [Hit Time]))

Not applicable
Author

Good idea, but still returning incorrect values:

There are only 1986 total hits, so these figures are impossible. Any other ideas?!

Matt

Not applicable
Author

I don't believe that you can assign a field another field in set analysis. (I've tried with the same results you're getting). I believe what you need to do is create a 'set' of values. to assign to the field. You could try creating a set of values from the current selection using CONCAT()

count({<[Calendar Date] = {CONCAT([Registration Date], ',')>}} [Hit Time])


Sorry, my syntax is likely wrong. You probably need to use a dollar sign expansion to evaluate the concat function… I bang my head against that syntax pretty often. You'd think I'd learn…