Incremental Load - field not found

[bushpalaamarnat]

I have two files with date 19 Jan 2018 and 26 Jan 2018, using incremental load concept created the following qlikview script. However,  the error message come as field Date_Load not found. Could you help.

The idea is every week, we get a file and want to upload the data into qlikview, as  the data gets refreshed every week and in qlikview, we want to see the status of inventory last week, this week and future estimate week.

Following is the script. Could you help.

ICR_allexcel:

LOAD

FILE_NAME,

     Day,

     Month,

     Year,

     Date_load,

     Week,

     Material,

     [Material Description],

     [Material Type],

     Category,

     [Inventory Volume],

     [Demand Volume (Eaches)],

     Brand,

     Variant,

     [Customer Loc],

     [Customer Location Description],

     [Customer Zone],

     [Supplier Loc],

     [Supplier Location Description],

     [Supplier Zone],

     [Corridor Status],

     DFC,

     [Min DFC],

     [Max DFC],

     [Safety Days Supply]

FROM

[..\Output_QVD\allexcels.qvd]

(qvd);

//Find last load date

Last_updated_date:

load max(Date_load) as Max_Dateload

resident ICR_allexcel;

//Store last modified date to a variable

Let Max_Dateload = peek('Max_Dateload',0,'Last_updated_date');

// Delete table

Drop table ICR_allexcel;

//Load incremental Data

ICR_incremental:

LOAD

     FileName() as FILE_NAME,

     Day(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))    as Day,

Month(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))                    as Month,

Year(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))                     as Year,

Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY')       as Date_load,

Week,

     Material,

     [Material Description],

     [Material Type],

     Category,

     [Inventory Volume],

     [Demand Volume (Eaches)],

     Brand,

     Variant,

     [Customer Loc],

     [Customer Location Description],

     [Customer Zone],

     [Supplier Loc],

     [Supplier Location Description],

     [Supplier Zone],

     [Corridor Status],

     DFC,

     [Min DFC],

     [Max DFC],

     [Safety Days Supply]

FROM

[..\Data Extract\26 Jan 2018.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Date_load > $(Max_Dateload);

//Concatenate with QVD

ICR_AllExcels:

Concatenate

Load

FILE_NAME,

     Day,

     Month,

     Year,

     Date_load,

     Week,

     Material,

     [Material Description],

     [Material Type],

     Category,

     [Inventory Volume],

     [Demand Volume (Eaches)],

     Brand,

     Variant,

     [Customer Loc],

     [Customer Location Description],

     [Customer Zone],

     [Supplier Loc],

     [Supplier Location Description],

     [Supplier Zone],

     [Corridor Status],

     DFC,

     [Min DFC],

     [Max DFC],

     [Safety Days Supply]

FROM

[..\Output_QVD\allexcels.qvd](qvd);

Store ICR_incremental into allexcels.qvd(qvd);

drop table ICR_incremental;

[YoussefBelloum]

Hi,

when you start to load only the first part of the script which is the last QVD, do you see the Date_load column ?

[bushpalaamarnat]

hi, Yes, i can see.

[bushpalaamarnat]

The error message is showing when the script is loading ICR_incremental table. that is not reading Date_load and showing error Date_Load not found

[YoussefBelloum]

Yes I understand, but I'm not seeing the issue here

[bushpalaamarnat]

I have tried different options, how ever the error is still persistent. Could any one help.

[zebhashmi]

Are you putting Table name in 'xxxx' single quotation marks everywhere within Peek?

[bushpalaamarnat]

//Store last modified date to a variable

Let Max_Dateload = peek('Max_Dateload',0,'Last_updated_date');

[big_dreams · Accepted solution]

Hi,

Yes there is error in your script.

I don't see Date_load field in you base 26 Jan 2018.xlsx

You are deriving it using this expression

Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY') as Date_load

You can not use derived field directly in same load.

Try with below modification.

ICR_incremental:

LOAD

      FileName() as FILE_NAME,

     Day(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))    as Day,

Month(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))                    as Month,

Year(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))                     as Year,

Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY')       as Date_load,

Week,

     Material,

     [Material Description],

     [Material Type],

     Category,

     [Inventory Volume],

     [Demand Volume (Eaches)],

     Brand,

     Variant,

     [Customer Loc],

     [Customer Location Description],

     [Customer Zone],

     [Supplier Loc],

     [Supplier Location Description],

     [Supplier Zone],

     [Corridor Status],

     DFC,

     [Min DFC],

     [Max DFC],

     [Safety Days Supply]

FROM

[..\Data Extract\26 Jan 2018.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY') > $(Max_Dateload);

Regards

[stabben23]

could it be as simple as this? Bold changes.

Date(Date(Date#(left(FileName(),11),'DD MMM YYYY'),'DD/MM/YY'))       as Date_load,