Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to calc some data in the script but I don't know in advance if the fields will exist in the resident table that I'm loading from
the script for the calc that I need to run is
Left join(Order_line)
Load
ID,
Requierd_Qty/Quantity_Units_In_Pallet as [Total Pallets],
floor(Requierd_Qty/Quantity_Units_In_Pallet) as [Total Pallets - Full],
(Requierd_Qty-(floor(Requierd_Qty/Quantity_Units_In_Pallet)*Quantity_Units_In_Pallet)) as [Total Units not in pallet]
//including layers
Resident Order_line;
Left join(Order_line)
Load
ID,
Requierd_Qty/Quantity_Units_In_Layer as [Total Layers],
[Total Units not in pallet]/Quantity_Units_In_Layer as [Total Layers not in Pallet],
floor([Total Units not in pallet]/Quantity_Units_In_Layer) as [Total Layers not in Pallet - Full],
[Total Units not in pallet]-(floor([Total Units not in pallet]/Quantity_Units_In_Layer)*Quantity_Units_In_Layer)
as [Total Units not in Layer]//including Boxes
Resident Order_line;
Left join(Order_line)
Load
ID,
Requierd_Qty/Quantity_Units_In_Box as [Total Boxes],
[Total Units not in Layer]/Quantity_Units_In_Box as [Total Boxes not in Layer],
floor([Total Units not in Layer]/Quantity_Units_In_Box) as [Total Boxes not in Layer - Full],
[Total Units not in Layer]-(floor([Total Units not in Layer]/Quantity_Units_In_Box)*Quantity_Units_In_Box)
as [Total Units not in Box]//including Inners
Resident Order_line;
Left join(Order_line)
Load
ID,
Requierd_Qty/Quantity_Units_In_Inner as [Total Inners],
[Total Units not in Box]/Quantity_Units_In_Inner as [Total Inners not in Box],
floor([Total Units not in Box]/Quantity_Units_In_Inner) as [Total Inners not in Box - Full],
[Total Units not in Box]-(floor([Total Units not in Box]/Quantity_Units_In_Inner)*Quantity_Units_In_Inner)
as [Total Units not in Inner]//including Units
Resident Order_line;
The problem is that the following fields might not exist in the order line table
(One/Or more):
Quantity_Units_In_Pallet,
Quantity_Units_In_Layer,
Quantity_Units_In_Box,
Quantity_Units_In_Inner
Due to this issue, I'm trying to find a solution for a condition that will check if the field exists and if so will run the relevant script/scripts otherwise will skip to the next part
Thanks
you may try something like
for each field in 'fielld1','field2', ..... fieldn
if fieldno(field,tablenanme)>0 then
switch field
case field1
....
case field2
...
so you are sure the field are processed in the desired order as specified in the value list
(be careful, i didn't take care or real syntax in my example)
hello
load first row of your table
the use fieldnumber() function to determine which fields exists
store the names in variables
and use $() in your load
to load the first row of which table?
Each script part in the script above contain different calcs how it will work?
Possible logic like.
1: load metadata of qvd. i.e. loading qvd as xml file.
You will get list of columns in qvd.
then use if else create flag for field exists or not.
then use that flag in you final script.
Regards,
after loading your resident table
add something like
vField1=fieldnumber('Quantity_Units_In_Pallet','Order_line);
if vField1<>0, it means field exists
do that for each field
and you will be able to build the correct left join according to the result
I did not understand how to run it so I tried different script but for some reason, I'm getting errors
let vTableq = NoOfFields('Item_Master_Data');
for quan = 2 to vTableq
let fnameq = FieldName(quan,'Item_Master_Data');
if left((fnameq),14)='Quantity_Units' THEN
inner join(Order_line) //add item data for calculation
Load distinct SKU,
$(fnameq)
Resident Item_Master_Data;
ELSE
ENDIF;
IF fnameq='Quantity_Units_In_Pallet' then
Left join(Order_line)
Load ID, Requierd_Qty/Quantity_Units_In_Pallet as [Total Pallets],
floor(Requierd_Qty/Quantity_Units_In_Pallet) as [Total Pallets - Full],
(Requierd_Qty-(floor(Requierd_Qty/Quantity_Units_In_Pallet)*Quantity_Units_In_Pallet)) as [Total Units not in pallet]//including layers
Resident Order_line;
ELSEIF fnameq='Quantity_Units_In_Layer' then
Left join
Load ID, Requierd_Qty/Quantity_Units_In_Layer as [Total Layers],
[Total Units not in pallet]/Quantity_Units_In_Layer as [Total Layers not in Pallet],
floor([Total Units not in pallet]/Quantity_Units_In_Layer) as [Total Layers not in Pallet - Full],
[Total Units not in pallet]-(floor([Total Units not in pallet]/Quantity_Units_In_Layer)*Quantity_Units_In_Layer) as [Total Units not in Layer]//including Boxes
Resident Order_line;
Left join
Load ID, Requierd_Qty/Quantity_Units_In_Box as [Total Boxes],
[Total Units not in Layer]/Quantity_Units_In_Box as [Total Boxes not in Layer],
floor([Total Units not in Layer]/Quantity_Units_In_Box) as [Total Boxes not in Layer - Full],
[Total Units not in Layer]-(floor([Total Units not in Layer]/Quantity_Units_In_Box)*Quantity_Units_In_Box) as [Total Units not in Box]//including Inners
Resident Order_line;
ELSEIF fnameq='Quantity_Units_In_Inner' then
Left join
Load ID, Requierd_Qty/Quantity_Units_In_Inner as [Total Inners],
[Total Units not in Box]/Quantity_Units_In_Inner as [Total Inners not in Box],
floor([Total Units not in Box]/Quantity_Units_In_Inner) as [Total Inners not in Box - Full],
[Total Units not in Box]-(floor([Total Units not in Box]/Quantity_Units_In_Inner)*Quantity_Units_In_Inner) as [Total Units not in Inner]//including Units
Resident Order_line;
Else
ENDIF;
next quan;
let vTableq2 = NoOfFields('Order_line');
for quan2 = 2 to vTableq2
let fnamequn = FieldName(quan2,'Order_line');
If left((fnamequn),14)='Quantity_Units' THEN
drop Field fnamequn from Order_line;
else
ENDIF;
next quan2;
ELSE
ENDIF;
hello
at first sight, that seems correct
which errors do you get ?
I'm getting an error for
"Field not found error
Field 'Total Units not in pallet' not found"
and
Field not found error
Field 'Total Units not in Layer' not found
and for some reason, the script does not drop the "Quantity_Units_In_Layer " field
as your field names contains spaces, don't forget to enclose them in []
in you drop field, use
drop field $(fnamequn)
I enclosed the fields with [] but still getting the error I think that the reason for the error is the order of the loading
so I'm getting the "Total Units not in pallet' not found" error for the loading of the "fnameq='Quantity_Units_In_Layer' then" part and so on
(there is a way to force the script to load the data by the same order that it's been loaded originally?)
I attached the "Script execution" log below
(The duplicates loading lines are for data autogeneration)
regarding the drop field $(fnamequn) I tried it also but the same result it's surprising because the script success to drop the rest of the "Quantity_Units*" fields
Script execution log below:
INLFED << d224e179-8f3a-41df-ba59-0a7075c3c7bc 4 Lines fetched
INLFED << 9f58ef1b-ed9e-46a7-a072-109f09e19fc3 1 Lines fetched
INLFED-1 << aa4f5c88-933c-486b-85b9-fb951433f598 4 Lines fetched
Item_Master_Data << Item_Master_Data (row-based qvd optimized) 222 Lines fetched
avgitemtable << Item_Master_Data 222 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Item_Master_Data 21 Lines fetched
Items_growing_1 << Item_Master_Data 21 Lines fetched
Inventory << Inventory 8,725 Lines fetched
Inventory 8,725 Lines fetched
Item_Master_Data 222 Lines fetched
avgnvtable << Inventory 8,725 Lines fetched
Inventory 20 Lines fetched
Inventory 20 Lines fetched
Inventory 20 Lines fetched
Inventory 20 Lines fetched
Inventory 20 Lines fetched
Inventory 20 Lines fetched
avgnvtable 20 Lines fetched
Temp_Inv_Dates_1 << Inventory 190 Lines fetched
Temp_Inv_Dates_2 << Temp_Inv_Dates_1 20 Lines fetched
Temp_Inv_Dates_3 << Temp_Inv_Dates_2 20 Lines fetched
InventoryTemp_Gen << Item_Master_Data 0 Lines fetched
Inventory 8,725 Lines fetched
Inventory 190 Lines fetched
OrderHeader << OrderHeader (row-based qvd optimized) 7,160 Lines fetched
Order_line << Order_line 119,844 Lines fetched
Item_Master_Data 222 Lines fetched
Field 'Total Units not in Layer' not found
Item_Master_Data 222 Lines fetched
Field 'Total Units not in pallet' not found
Item_Master_Data 222 Lines fetched
Order_line 119,429 Lines fetched
Customers << Customers (row-based qvd optimized) 70 Lines fetched
Prcnt << 6ff95755-7d52-4a05-81a0-e191185a6a18 20 Lines fetched
HoursTable << 30f1ad67-cb09-4c13-b14c-9b4d813f5076 24 Lines fetched
$Syn 1 = Quantity_Units_In_Layer+SKU