Resident Tab1 and create new column in scripting

[My_Rebecca]

The new column "GR_Net Working Days" needs to be created on the basis of "Truck arrival" of Tab1.

How to create this new column by "Resident"? 

Tab1:

TJGRMap1:
Mapping Load
"GR Number",
"Truck arrival"
FROM ***;

LOAD
"Material Document" as "GR Number",
"Posting Date",
Date(ApplyMap('TJGRMap1',"Material Document", null()), 'YYYY-MM-DD h:mm:ss') as "Truck arrival"
FROM ***;

Tab2:
NoConcatenate LOAD Distinct*Resident Tab1
if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date])-1 as "GR_Net Working Days"

[justISO · Accepted solution]

Hi, you need describe wanted fields between LOAD and RESIDENT. In your case:

Tab2:
NoConcatenate 
LOAD Distinct
*,
if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date])-1 as "GR_Net Working Days"
Resident Tab1;

drop table Tab1;

[My_Rebecca]

Error occurs. Could you please support to correct it? thanks.

[justISO]

sorry, I missed semicolon, should be

Resident Tab1;

[My_Rebecca]

Thanks. But another error occurs, how to find Tab1? Is there anything wrong in editing Tab1?

[justISO]

"Tab1" is a reference to your first table name you taking data from (by resident). Maybe it is not Tab1 (digit 1), but Tabl (lower letter L)?

[My_Rebecca]

I try to change the Tab1 to above first Load and succeed. Do you think it's right?

TJGRMap1:
Mapping Load
"GR Number",
"Truck arrival"
FROM ***;

Tab1:

LOAD
"Material Document" as "GR Number",
"Posting Date",
Date(ApplyMap('TJGRMap1',"Material Document", null()), 'YYYY-MM-DD h:mm:ss') as "Truck arrival"
FROM ***;

Tab2:
NoConcatenate
LOAD Distinct*,
if(hour([Truck arrival])>=15,-1,0)+NetWorkDays([Truck arrival],[Posting Date])-1 as "GR_Net Working Days"
Resident Tab1;
Drop table Tab1;

[justISO]

Seems yes, as now your mapping table is called 'TJGRMap1', first table is called 'Tab1' and further you create new table from 'Tab1' (called 'Tab2') and delete 'Tab1' as it is not needed anymore (all data is in 'Tab2' already).

[My_Rebecca]

Now it works. Thanks for your support.