topic Filter Date Between Two Uncoupled Tables in QlikView

Filter Date Between Two Uncoupled Tables

Wed, 23 Jun 2010 17:29:08 GMT

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:

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).

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

Best,

Matt

Filter Date Between Two Uncoupled Tables

Anonymous — Wed, 23 Jun 2010 17:59:37 GMT

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

Filter Date Between Two Uncoupled Tables

Wed, 23 Jun 2010 18:10:11 GMT

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

Best,

Matt

Filter Date Between Two Uncoupled Tables

Anonymous — Wed, 23 Jun 2010 18:23:22 GMT

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]))

Filter Date Between Two Uncoupled Tables

Wed, 23 Jun 2010 18:36:12 GMT

Good idea, but still returning incorrect values:

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

Matt

Filter Date Between Two Uncoupled Tables

Wed, 23 Jun 2010 19:25:57 GMT

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…