topic Re: Logic Help - Join two tables based on date interval in QlikView

Logic Help - Join two tables based on date interval

raju_salmon — Sat, 16 Nov 2024 01:44:15 GMT

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

Re: Logic Help - Join two tables based on date interval

Vegar — Tue, 10 Dec 2019 12:39:31 GMT

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;

Re: Logic Help - Join two tables based on date interval

raju_salmon — Tue, 10 Dec 2019 14:12:38 GMT

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.

Re: Logic Help - Join two tables based on date interval

Vegar — Wed, 11 Dec 2019 16:01:06 GMT

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.

Re: Logic Help - Join two tables based on date interval

Brett_Bleess — Tue, 17 Dec 2019 21:08:15 GMT

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

Re: Logic Help - Join two tables based on date interval

raju_salmon — Fri, 03 Jan 2020 09:50:23 GMT

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