Creating a field in Data Editor using Resident

[kaitlynwagner_s]

Hi all! I am working on a project and being a bit green am not sure if I am writing syntax correctly. I am currently loading two QVDs (one containing Inventory Orders, and one containing Dates to those Inventory orders) using a Load * and Left Keep statement. The reason i am doing a Left Keep is bc the data quality in Orders isnt great, so I need to be able to see where I have order dates for inventory that were never created in the orders table. 

Data is as such right now:

OrderData:

LOAD
FileRef,
FileType,
FileBLNB,
FileCustomer,
FileGUID,

OrderDate,
OrderGUID

FROM [lib://File_Imports/OrderData.qvd]
(qvd);

LEFT KEEP OrderDates:
LOAD
OrderGUID,
OriginCity,

OriginState,

DestCity,

DestState,

LoadDate,

PickupDate, 

ArrivalDate,

UnloadDate

FROM [lib://File_Imports/OrderDeliveryData.qvd]
(qvd);

Now here is where I am stuck; we need to create a status based on the dates, and doing it as a dimension gets complicated so I wanted to add it in the  data editor script (theres a bunch of these but just showing the first one):  

Load *,
If(
IF(LEN(OrderDate) > 1
and LEN( LoadDate) < 1
and LEN(PickupDate) < 1
and LEN(ArrivalDate) < 1
and LEN(UnloadDate) < 1 ,'1 - OrderCreated',

If(
IF(LEN(OrderDate) > 1
and LEN( LoadDate) >1
and LEN(PickupDate) < 1
and LEN(ArrivalDate) < 1
and LEN(UnloadDate) < 1 ,'1 - OrderLoaded',

)) as OrderStattus

RESIDENT OrderData;

The error i get is that LoadDate and PickupDate and Arrival and Unloaded are not part of "OrderData" table even though i have done a join; is there a way to fix this?

I get the 

[Digvijay_Singh]

AFAIK Left Keep is not actually joining the table, You would need to use LEFT JOIN instead - 

Documentation - 

The keep prefix is similar to the join prefix. Just as the join prefix, it compares the loaded table with an existing named table or the last previously created data table, but instead of joining the loaded table with an existing table, it has the effect of reducing one or both of the two tables before they are stored in Qlik Sense, based on the intersection of table data. 

Ensure the common field name is as per the need between both table. 

Thanks,

[kaitlynwagner_s]

Thank you for the advice, however if i do a LEFT JOIN i will be missing data from the OrderDates table where I need to see what is "Missing" (Simply, where do I have NULLS to the Orders table, and where do I have nulls on the OrderDates table). Typically in SQL I would write a FULL OUTER JOIN where I could see both Matches and Non Matches. In this case, can Qlik do the same with the OUTER function? 

[Digvijay_Singh]

Yes.

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...