topic Re: Create Flag/New Field from 2 Different QVDs in QlikView

Create Flag/New Field from 2 Different QVDs

Fri, 10 Feb 2017 20:39:06 GMT

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.

Re: Create Flag/New Field from 2 Different QVDs

Qrishna — Fri, 10 Feb 2017 23:31:14 GMT

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

];

Re: Create Flag/New Field from 2 Different QVDs

sdmech81 — Sat, 11 Feb 2017 02:59:52 GMT

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

Re: Create Flag/New Field from 2 Different QVDs

Sun, 12 Feb 2017 03:39:54 GMT

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

Re: Create Flag/New Field from 2 Different QVDs

Sun, 12 Feb 2017 03:40:30 GMT

Yes both tables have Customer ID as a common field.

Re: Create Flag/New Field from 2 Different QVDs

sdmech81 — Sun, 12 Feb 2017 12:01:05 GMT

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

Re: Create Flag/New Field from 2 Different QVDs

MarcoWedel — Sun, 12 Feb 2017 21:17:24 GMT

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

Re: Create Flag/New Field from 2 Different QVDs

Mon, 13 Feb 2017 13:47:52 GMT

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', '-') ;

Re: Create Flag/New Field from 2 Different QVDs

Mon, 13 Feb 2017 13:52:32 GMT

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

[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', '-') ;