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: 
hd1
Contributor III
Contributor III

Load aggregation of two variables from different tables

Hi, 

I have two tables that look like this 

Preorder: preorderDate, orderNumber,userId, itemNumber, ItemDescription. 

Payment:paymentDate, orderNumber,userId, itemNumber, amountPayed, AmountSent.

 

I am trying to calculate the difference between preorderDate and paymentDate in a different table .

like this :

num(date(floor(paymentDate )))- num(date(floor(preorderDate )))

I tried to apply concatenate and mapping function but nothing works.

I get different errors based on the different approaches i took; does anyone have any solutions for this problem?

 

Many thanks,

1 Solution

Accepted Solutions
sunny_talwar

Let's just look at the first attempt...

TableTemp:
LOAD OrderNumber
     Num(Date(Floor([PreorderDate]))) as preorderDateNumber;
SELECT OrderNumber
       PreorderDate
FROM DBMS."Preorders";

Left Join (TableTemp)
SQL SELECT PaymentDate
FROM DBMS."Payments";

Do you have a single PaymentDate for all OrderNumbers? If not, then why are you not joining Payments to Preorders on OrderNumber? Right now... all you are doing is to join every single PaymentDate to every possible OrderNumber... this is a Cartesian product join... which will multiply your data several times.

View solution in original post

4 Replies
sunny_talwar

Would you be able to share the exact script you are trying?

hd1
Contributor III
Contributor III
Author

Sure!
First Attempt using left Join: 
TableTemp:
Load
OrderNumber
num(Date(Floor([PreorderDate]))) as preorderDateNumber;
SELECT OrderNumber
PreorderDate
FROM DBMS."Preorders";
LEFT JOIN (TableTemp)
SQL SELECT PaymentDate
FROM DBMS."Payments" ;

preOrderToPayment:
Load
preorderDateNumber - num(Date(Floor(PaymentDate))) as P2PTime
Resident TableTemp;
Drop Table TableTemp;

Second Attempt using Concatenate:

Load
OrderNumber
num(Date(Floor([PreorderDate]))) as preorderDateNumber;
SELECT OrderNumber
PreorderDate
FROM DBMS."Preorders";

Concatenate Load
Date(Floor(PaymentDate)) as paymentDate ,

preorderDateNumber - num(Date(Floor(PaymentDate))) as P2PTime;
SELECT
PaymentDate

FROM DBMS."Payments";

In both i get the error that 'preorderDateNumber' doesn't exist

 

I also did try MAP but that didnt work either 

Many thanks 

sunny_talwar

Let's just look at the first attempt...

TableTemp:
LOAD OrderNumber
     Num(Date(Floor([PreorderDate]))) as preorderDateNumber;
SELECT OrderNumber
       PreorderDate
FROM DBMS."Preorders";

Left Join (TableTemp)
SQL SELECT PaymentDate
FROM DBMS."Payments";

Do you have a single PaymentDate for all OrderNumbers? If not, then why are you not joining Payments to Preorders on OrderNumber? Right now... all you are doing is to join every single PaymentDate to every possible OrderNumber... this is a Cartesian product join... which will multiply your data several times.

Brett_Bleess
Former Employee
Former Employee

If Sunny's last post got you on the right track with things, do not forget to return to the post and give him credit for the help by using the Accept as Solution button on his post.  If you did something different, please consider posting that, and then use the button on that post instead to close out the thread to let other Members know what did work.  If you still have questions, please leave an update, but do please try to close things out.

There is one thing I can leave you, below is a link to the Design Blog are of Community, there are lots of posts in this area regarding how-to items that may give you ideas on this or future situations, so be sure to bookmark this one for future reference.

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

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.