Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
Would you be able to share the exact script you are trying?
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
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.
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