Logic Help - Join two tables based on date interval

[raju_salmon]

Hi Experts,

I am looking for some logic help. I have two QVDs.

1. Email: EmailSentDate is available for each customer (customerID is getting repeated)

2. Orders: Date1, Date2 fields are there, Flag should populate YES/NO if EmailSentDate falls between these two dates for the same customer.

I want to create a new flag in Orders QVD based on data available in Email QVD. Could you please have a look at the attachment and suggest me how can we achieve this output?

Regards,

Raju

[Vegar · Accepted solution]

You can try something like this.

Email:
LOAD
  CustomerID,
  EmailSentDate
FROM
  [..\Downloads\Data.xlsx]
  (ooxml, embedded labels, table is Email)
Where  
  EmailSentDate > 0;
;

TmpOrders:
LOAD
  CustomerID,
  Date1,
  Date2,
  Amount,
  Flag
FROM
  [..\Downloads\Data.xlsx]
  (ooxml, embedded labels, table is Orders)
WHERE
  CustomerID > 0
;

LEFT JOIN (TmpOrders)
IntervalMatch (EmailSentDate, CustomerID)
LOAD Date1, Date2, CustomerID
Resident TmpOrders;

drop table Email;

Orders:
LOAD
  CustomerID,
  Date1,
  Date2,
  Amount,
  Flag as ExpectedFlag,
  IF(EmailSentDate, 'YES', 'NO') as CalculatedFlag
Resident TmpOrders
;

Drop table TmpOrders;

[raju_salmon]

Thanks you, can you explain the reason for below filters?

Where
EmailSentDate > 0;

WHERE
CustomerID > 0

Note: I have implemented another way but it makes cross join first then filter the data based on flag. Not efficient way, so will try your solution.

[Vegar]

I had some issues with null value rows in my script reading from excel so to eliminate these I just made sure that I fetched valid rows of data.

[Brett_Bleess]

Raju, if Vegar's solution did work for you, please be sure to return to your post and use the Accept as Solution button on his post to give him credit for the help and to let other Community Members know it did work for you.  If you are still trying to figure something out, leave an update on things.  

The only other thing I have that may be helpful would be the Design Blog area of Community, this is a repository of hundreds of how-to posts on all sorts of topics.  

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[raju_salmon]

Thanks for the heads-up. Solution got worked very well. Thanks.