Calculated field based on related table

[Anonymous]

Hello everyone.

Let's say I have two tables:

OrderList:

Order_Id

Order_Start_Date

Order_End_Date

OrderDetails:

Order_Id

Order_Item

Order_Quantity

These two tables are linked together by a One to Many (Order list 1 - * OrderDetails) relationship by Order_Id.

What I'd like to do is to create a calculated field in OrderDetails, such that

IF(Order_Start_Date < Order_End_Date, 'Right', 'Wrong') .

It would be easy to do that in the OrderList table, but how could I do it in OrderDetails?
I am coming from another BI Software and I was used to work this way.

Is it possible with Qlik Sense or do I have to merge the two tables in the script editor and create a single table?

In the data editor I can choose only the fields ot the table I am currently working on.

Many thanks to everyone who may give me some hints about this!

[YoussefBelloum · Accepted solution]

Hi,

you can join ORDER_START_DATE and ORDER_END_DATE to the ORDER DETAILS table, calculate your NEW FIELD, after that if you want you can DROP the orders dates fields from it.

[Anonymous]

Yes, that could do.

There is no other way outside of the script editor, right?

[YoussefBelloum]

if you talks about data modeling on Qlik, you should do it on the script of course.

otherwise if your data comes from another ETL transformations, you can make these transformations before you load your tables on Qlik.

[Anonymous]

Understood, thanks.