Data Load Error

[My_Rebecca]

After ApplyMap "Truck Arrival", then write expression on the basis of this "Truck Arrival" while loading, but error occurs.

How to correct it? Thanks.

LOAD "Material Document" as "GR Number",
Supplier as "Vendor Code",
Plant,
"Storage Location",
"Movement Type",
"Material Document",
"Material Doc.Item",
"Posting Date",
"Time of Entry",
"Special Stock",
Supplier,
Material,
"Material Description",
Quantity,
"Unit of Entry",
"Amount in LC",
"Purchase Order",
Item,
"Sales Order",
"Sales order item",
ApplyMap('TJGRMap1',"Material Document", null()) as "Truck arrival",
ApplyMap('TJGRMap2',Supplier,null()) as "Supplier Name",
ApplyMap('DC_TJ_GR',Supplier,null()) as "Import/Domestic",
if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date]) as "DC TJ GR On-time/Late"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

[hic · Accepted solution]

That's correct. "Truck arrival" does not exist in the input to the Load, and can not be referenced. A general rule in the Qlik scripting is that field references inside a Load must refer to the fields in the input table – the source of the Load statement. The aliases from the same load cannot be referenced.

Try a preceding  Load instead:

Load 
   if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date]) as "DC TJ GR On-time/Late",
   ... ;

Load 
   ApplyMap('TJGRMap1',"Material Document", null()) as "Truck arrival",
   ...
   From ... ;
   

https://community.qlik.com/t5/Qlik-Design-Blog/Preceding-Load/ba-p/1469534 

[marcus_sommer · Accepted solution]

All fields needs to be exists in the source - access anything new created isn't possible. Therefore you need to apply the applymap() also by the networkdays() evaluation or you used a preceding load, like:

load *, 
   if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date]) as "DC TJ GR On-time/Late";
LOAD "Material Document" as "GR Number",
Supplier as "Vendor Code",
Plant,
"Storage Location",
"Movement Type",
"Material Document",
"Material Doc.Item",
"Posting Date",
"Time of Entry",
"Special Stock",
Supplier,
Material,
"Material Description",
Quantity,
"Unit of Entry",
"Amount in LC",
"Purchase Order",
Item,
"Sales Order",
"Sales order item",
ApplyMap('TJGRMap1',"Material Document", null()) as "Truck arrival",
ApplyMap('TJGRMap2',Supplier,null()) as "Supplier Name",
ApplyMap('DC_TJ_GR',Supplier,null()) as "Import/Domestic"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

[henrikalmen · Accepted solution]

The line that starts with if(hour([Truck arrival])>=15,-1,0) is not aware of a field called Truck arrival since that field is created within the same statement. You could either do this:

if(hour( ApplyMap('TJGRMap1',"Material Document", null()) )>=15,-1,0) ...

or you could remove that line completely, and add it above the load statement. It's called a preceding load, like this:

load *, if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date]) as "DC TJ GR On-time/Late"; //this is a "preceding load", it can reference fields generated in the load below it
LOAD "Material Document" as "GR Number",
Supplier as "Vendor Code",
...
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/GR/SAP/DC TJ GR_2*.XLSX]
(ooxml, embedded labels, table is Sheet1);

[My_Rebecca]

@henrikalmen @marcus_sommer @hic 

Dear all,

What about Preceding Load (certain expression processing) on the basis of "Preceding Load"? Thanks.

Follwoing example: "DC TJ GR On-time/Late" is the second preceding load on the basis of the first preceding load "DC TJ GR Net Working Days".

I currently select to embed the first preceding load into the second preceding load, but error occurs as too long calculation.

LOAD*,
if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date],)-1 as "DC TJ GR Net Working Days",
if(IsNull(if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date],-1),'Factory Direct Delivery',if((if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date],)-1)>0,'Late','On-time')) as "DC TJ GR On-time/Late";

LOAD "Material Document" as "GR Number",
Supplier as "Vendor Code",
Plant,
"Storage Location",
"Movement Type",
"Material Document",
"Material Doc.Item",
"Posting Date",
"Time of Entry",
"Special Stock",
Supplier,
Material,
"Material Description",
Quantity,
"Unit of Entry",
"Amount in LC",
"Purchase Order",
Item,
"Sales Order",
"Sales order item",
Date(ApplyMap('TJGRMap1',"Material Document", null()), 'YYYY-MM-DD h:mm:ss') as "Truck arrival",
ApplyMap('TJGRMap2',Supplier,null()) as "Supplier Name",
ApplyMap('DC_TJ_GR',Supplier,null()) as "Import/Domestic",

[henrikalmen]

Your problem seems to be not the preceding loads but the rest of your data model. The messages in yellow in the image you're posting are saying you have a lot of synthetic keys. They occur when you have more than one field with the same name in more than one table. I would guess that if you resolve those issues, you will not have timeouts.

[My_Rebecca]

Yes, you're right. There are truly many columns which have the same tile among 3 sheets.

My opinion is to change to the single column name among these sheets, then load again.

Do you think my opinion is OK? Thanks.

[henrikalmen]

It's not so much about the contents of the sheets, it's about field
names in the different tables that you are loading. You should make sure
that when the load script finishes, only one field in each table links
to another table. You need to change the load script first, then you can
take a look at the objects you have created on your sheets and make sure
the field names are updated there as well.