Problem with dates: Solve in set analysis or the load

[#1]

Hi All,

In the next example I'd like to solve a problem with dates from 2 tables. (And it's giving me headaches since a week)

In table "Actuals" we have customers(Site_id) with actuals for different dates.

In table Interactions we have interactions (if there are any) of these customers.

Now I'd like to see the interactions on the dates which are relevant for the date of the actual.

Example 1:

The yellow marked are the ones I'd like to see combined.

So, for instance for Actual date 6-5-2016 it only makes sense that the interaction date 9-5-2016 is corresponding.

Because the interaction on the 11-5-2016 is for another actual.

Example 2:

Here it makes no sense that there is an Interaction_Date of 3-5-2016 linked to the actual date of 17-5-2016 (because that's not possible of course)

************

Attached the test data in Excel and the test App.

Hopefully somebody has an idea to either fix this in the load or with set analysis.

Thanks in advance...

KR,

Paul

[sunny_talwar]

Not sure I understand, what is the expected output here?

[Anonymous]

Hi

Are you looking for the nearest actual date which is either less than or equal to the interaction date for each interaction? As in your example interaction 03.05.16 should be associated with the actual date of 03.05.16 but not with any actual date that is greater than the interaction date? Does that make sense?

[#4]

Hi Sunny,

It should look like this...

So, the green combinations are valid / the red ones not.

Rules:

1. Interaction_Date >= Actual_Date (Cell C6 compared to Cell B6)

2. Interaction_Date NOT > Next Actual date  (Cell C2/C3/C4 compared to Cell B6)

3. Show also Actual dates where there NO Interaction dates (yellow marked Cell I6...)

Hope it's more clear now

KR,

Paul

[sunny_talwar]

Was not able to get the last requirement done, but rest 2 are working for QV12.

If(Interaction_Date >= Actual_Date and Interaction_Date < Aggr(NODISTINCT Below(Actual_Date), Site_Id, (Actual_Date,(Numeric))), Sum(Interaction))

[#6]

Okay...

Your solution is the outcome, indeed without the last requirement.

Thanks for showing it's possible

Unfortunately we're using Q11.20.xxxx

So, the expression does not work...

***************************************************************************************************

I think I'm going to try to load it with an FOR - NEXT statement.

But also here is something going wrong...

AND I'm not sure how the NOCONCATENATE .... WHERE NOT EXISTS works exactly

Please have a look at my LOAD...

I can load all data but it also gives me back 30+ tables!

Cheers

[sunny_talwar]

The reason it doesn't work in QV11.2 IS because I am using a new thing that is only available for QV12 and Qlik Sense

The sortable Aggr function is finally here!‌. You might be able to get this to work, if you can get the sort order right in the script.

I won't have the time to troubleshoot your script, but I hope some else might be able to chip in