topic Load aggregation of two variables from different tables in QlikView

Load aggregation of two variables from different tables

hd1 — Sat, 16 Nov 2024 19:10:57 GMT

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,

Re: Load aggregation of two variables from different tables

sunny_talwar — Tue, 04 Feb 2020 12:35:42 GMT

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

Re: Load aggregation of two variables from different tables

hd1 — Wed, 05 Feb 2020 07:08:00 GMT

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

Re: Load aggregation of two variables from different tables

sunny_talwar — Wed, 05 Feb 2020 13:21:06 GMT

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.

Re: Load aggregation of two variables from different tables

Brett_Bleess — Tue, 03 Mar 2020 20:54:03 GMT

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