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;

[stabben23]

This is the problem!

[big_dreams]

why you required another date()???

[tamilarasu]

Hi Neelima,

big_dreams is right. You can not use derived field in same load. Also thought of sharing with you below points.

1). Note that Max function returns number format. So you need format it back to Date format.

//Find last load date
Last_updated_date:
Load Date(max(Date_load), 'DD/MM/YY')  as Max_Dateload
resident ICR_allexcel;

2. You no need to use left and date function to create new fields like Day, Month, Year and Date. You can use below code.

FileBaseName()                                        as FILE_NAME,
Day(Date#(FileBaseName(),'DD MMM YYYY'))              as Day,
Month(Date#(FileName(),'DD MMM YYYY'))                as Month,
Year(Date#(FileBaseName(),'DD MMM YYYY'))             as Year,
Date(Date#(FileBaseName(),'DD MMM YYYY'),'DD/MM/YY')  as Date_load

3)  You have to change your where condition below

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

4) This point is not important but may be useful for you. You have some other default functions to store path, Dir, Filetime etc.

Good luck.

[bushpalaamarnat]

when i created a new qlikview the same script worked out. Thank you all for the support.

[big_dreams]

I am curious ...

how that work??? did you make any changes in your script which you posted in your original script.

Regards,

[tamilarasu]

Neelima Bushpala wrote:

when i created a new qlikview the same script worked out. Thank you all for the support.

I don't think so. I believe you have modified your script as suggested by Max else you should get error message.