topic Re: Incremental Load - field not found in QlikView

Incremental Load - field not found

bushpalaamarnat — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Incremental Load - field not found

YoussefBelloum — Mon, 05 Feb 2018 13:51:26 GMT

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 ?

Re: Incremental Load - field not found

bushpalaamarnat — Mon, 05 Feb 2018 14:17:06 GMT

hi, Yes, i can see.

Re: Incremental Load - field not found

bushpalaamarnat — Mon, 05 Feb 2018 14:27:46 GMT

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

Re: Incremental Load - field not found

YoussefBelloum — Mon, 05 Feb 2018 14:32:19 GMT

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

Re: Incremental Load - field not found

bushpalaamarnat — Mon, 05 Feb 2018 15:12:42 GMT

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

Re: Incremental Load - field not found

zebhashmi — Mon, 05 Feb 2018 15:27:47 GMT

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

Re: Incremental Load - field not found

bushpalaamarnat — Tue, 06 Feb 2018 11:17:27 GMT

//Store last modified date to a variable

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

Re: Incremental Load - field not found

big_dreams — Tue, 06 Feb 2018 11:24:59 GMT

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

Re: Incremental Load - field not found

stabben23 — Tue, 06 Feb 2018 11:26:07 GMT

could it be as simple as this? Bold changes.

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

Re: Incremental Load - field not found

stabben23 — Tue, 06 Feb 2018 11:28:03 GMT

This is the problem!

Re: Incremental Load - field not found

big_dreams — Tue, 06 Feb 2018 11:45:19 GMT

why you required another date()???

Re: Incremental Load - field not found

tamilarasu — Tue, 06 Feb 2018 11:48:02 GMT

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.

Re: Incremental Load - field not found

bushpalaamarnat — Tue, 06 Feb 2018 12:03:14 GMT

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

Re: Incremental Load - field not found

big_dreams — Tue, 06 Feb 2018 12:06:27 GMT

I am curious ...

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

Regards,

Re: Incremental Load - field not found

tamilarasu — Tue, 06 Feb 2018 12:07:39 GMT

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.