Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Creating a Column in a Joined Table

Hi All,

I'm attempting to calculate the number of days on lot for our inventory records.  This is calculated in the following way:

[Sale Date]  -  [Arrival Date]
If no sale date,  Today - [Arrival Date]

If it hasn't arrived, Days on Lot should be null

I've implemented this in the following manner in my Qlikview Script.  It's pulling data from the Arrival Date and Sale Date table and populating accordingly.

OrderLines:

LOAD *,

IF(ISNULL([SaleDate]),(Today()-[ArrivalDate]),IF(ISNULL([ArrivalDate]),null(),([SaleDate]-[ArrivalDate]))) as DOL;

LOAD RowNo() as OrderLineID, RTADD08_ORDER_GOTD_D, RTAD06_ORDER_ARRIVAL_DATE_K as ArrivalDateK, RTAD06_ORDER_SALE_DATE_K as SaleDateK

RESIDENT Order;

LEFT JOIN

LOAD RTAD06_ORDER_ARRIVAL_DATE_K as ArrivalDateK, ARRIVAL_FULL_USA as ArrivalDate

RESIDENT ArrivalDate;

LEFT JOIN

LOAD RTAD06_ORDER_SALE_DATE_K as SaleDateK, SALE_FULL_USA as SaleDate

RESIDENT SaleDate;

Unfortunately when I add my Days On Lot calculation ("DOL"), it says that the field 'SaleDate' cannot be found.  Any idea where I'm going about this wrong?  Do I need to make another table entirely?

Also note, this OrderLines table is an intermediary table to support SaleDate and ArrivalDate being linked to a canonical date per hic‌'s canonical date method.

Any help is greatly appreciated!  Thank you!

Tags (1)
Community Browser