Create Flag/New Field from 2 Different QVDs

[#1]

I'm looking to create a new field or flag using fields from 2 QVDs.  I can create it as a measure when creating a visualization in Qlik Sense but I'm looking to make it a dimension so I can use it as a filter selection.  If the statement reads true, I would like a 'Y' to generate in a field called 'On Time':

In Words:

If there is an Order# present and the Order was Processed (Y or N) and Shipped before its Due Date then it is On Time.

Expression as a measure:

If(len(trim([Order#]))>0 and [Order Processed] = 'Y' and

[Order Shipped Date] <= [Order Due Date], 'On Time', '-')

QVD 1:

Order#,

Order Processed,

Order Shipped Date

QVD 2:

Order Due Date

I am not sure how to write it out for the Data Load Editor or where to place it in my script. 

[Qrishna]

Any Sample Data to play around with?

Does the 1st and 2nd table has any fields in common?

Eg:

QVD1:

Load * Inline [

Order#,Order Processed,Order Shipped Date

];

QVD1:

Load * Inline [

Order#,Order Due Date

];

[sdmech81]

Hi,

You need field based on which they r realated.

For that first check wthr the relationship exists btn table 1 and 2 ..If so, join both the table based on tht relation.

Then After join you can create one New field you are looking for using resident load  and your above expression looks correct..Try..

Sachin

[#4]

Yes both tables have the common field 'Customer ID'.

[#5]

Yes both tables have Customer ID as a common field.

[sdmech81]

Then plss try doing as said above..

Hope ur expression is correct only..

If didn't get thn plss attach QVW ll try latr..

Sachin

[MarcoWedel]

Your  QVD 2 only has the [Customer ID] field in common with QVD 1 but contains an [Order Due Date]?

So you only ever have one order per customer at the same time or do all orders per customer share the same due date?

regards

Marco

[#8]

I loaded both tables and joined them by 'Customer ID' field.  I then placed the above expression in various places within my load script but was still getting an error.  Below is my load script and and expression, and the error in the syntax as noted by QLIK.  QLIK is saying my expression is wrong where I underlined and bolded in the expression.  Any suggestions?  Maybe I am placing it in the wrong place?

[Order_Details]:

LOAD [Customer ID],

  [Order Processed],

  [Order Shipped Date],

  [Product Name],

  [Order Scheduled Date]

FROM [lib://3. Shipping Global/Order_Details.qvd]

(qvd);

[Customer_Details]:

LOAD [Customer ID],

  [Customer Name],

  [Order Due Date]

FROM [lib://3. Shipping Global/Customer_Details.qvd]

(qvd);

If(len(trim([Vaccine Order Number]))>0 and [Vaccine Administered Flag] = 'Y' and [Vaccine Documented Date] <= [Discharge Date], 'GIVEN', '-') ;

[#9]

I apologize, I loaded the wrong script from another dashboard I am working on.  Here is the correct syntax:

I loaded both tables and joined them by 'Customer ID' field.  I then placed the above expression in various places within my load script but was still getting an error.  Below is my load script and and expression, and the error in the syntax as noted by QLIK.  QLIK is saying my expression is wrong where I underlined and bolded in the expression.  Any suggestions?  Maybe I am placing it in the wrong place?

[Order_Details]:

LOAD [Customer ID],

[Order Number],

  [Order Processed],

  [Order Shipped Date],

  [Product Name],

  [Order Scheduled Date]

FROM [lib://3. Shipping Global/Order_Details.qvd]

(qvd);

[Customer_Details]:

LOAD [Customer ID],

  [Customer Name],

  [Order Due Date]

FROM [lib://3. Shipping Global/Customer_Details.qvd]

(qvd);

If(len(trim([Order Number]))>0 and [Order Processed] = 'Y' and [Order Shipped Date] <= [Order Due Date], 'GIVEN', '-') ;