Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I would like to create fields that do not exist in the table for the below scenario
The following fields may exist in 'Item_Master_Data' table :
Quantity_Units_In_Pallet,
Quantity_Units_In_Layer,
Quantity_Units_In_Box,
Quantity_Units_In_Inner,
Quantity_Inners_In_Box,
Quantity_Inners_In_Layer,
Quantity_Inners_In_Pallet,
Quantity_Boxes_In_Layer,
Quantity_Boxes_In_Pallet,
Quantity_Layers_In_Pallet,
i have a script that checks if the fields are existing and if so join them to 'Order_line' table
i would like to auto create the missing field with '0' values
what is the most recommended way to do it?
the script that I'm using for joining the fields is:
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;
Thank you
for each field in (
'Quantity_Units_In_Pallet',
'Quantity_Units_In_Layer',
'Quantity_Units_In_Box',
'Quantity_Units_In_Inner',
'Quantity_Inners_In_Box',
'Quantity_Inners_In_Layer',
'Quantity_Inners_In_Pallet',
'Quantity_Boxes_In_Layer',
'Quantity_Boxes_In_Pallet',
Quantity_Layers_In_Pallet'
)
if fieldnumber(field,'Item_Master_Data')>0 then let fnameq = field
else let fnameq='0 as ' & field
end if
inner join(Order_line) //add item data for calculation
Load distinct SKU,
$(fnameq)
Resident Item_Master_Data;
next field
hello,
why don't you make a loop
for each field in (value list)
if fieldno(field)>0 then let fnameq = field
else let fnameq='0 as ' & field
inner(join)
.......
you make have to check syntax of my code
The 'Item_Master_Data' table may contain almost 100 column
I would like to try to optimize the runtime of the script by limiting the fields that will be checked to the field list that I wrote in the original post only
in my script, value list is the list of fields you want to check
Could you please explain how should I use it?
How the "value list" linked with the table 'Item_Master_Data'?
Hi,
maybe you can create a new column in the table as :
if (len(Item_Master_Data) > 0, Item_Master_Data, 0) as 'Item_Master_Data_1
Best,
for each field in (
'Quantity_Units_In_Pallet',
'Quantity_Units_In_Layer',
'Quantity_Units_In_Box',
'Quantity_Units_In_Inner',
'Quantity_Inners_In_Box',
'Quantity_Inners_In_Layer',
'Quantity_Inners_In_Pallet',
'Quantity_Boxes_In_Layer',
'Quantity_Boxes_In_Pallet',
Quantity_Layers_In_Pallet'
)
if fieldnumber(field,'Item_Master_Data')>0 then let fnameq = field
else let fnameq='0 as ' & field
end if
inner join(Order_line) //add item data for calculation
Load distinct SKU,
$(fnameq)
Resident Item_Master_Data;
next field
Thank you!
I found another way to do it (i think that my way is longer)
Let Q1= FieldNumber('Quantity_Units_In_Pallet','Order_line');
Let Q2= FieldNumber('Quantity_Units_In_Layer','Order_line');
Let Q3= FieldNumber('Quantity_Units_In_Box','Order_line');
Let Q4= FieldNumber('Quantity_Units_In_Inner','Order_line');
Let Q5= FieldNumber('Quantity_Inners_In_Box','Order_line');
Let Q6= FieldNumber('Quantity_Inners_In_Layer','Order_line');
Let Q7= FieldNumber('Quantity_Inners_In_Pallet','Order_line');
Let Q8= FieldNumber('Quantity_Boxes_In_Layer','Order_line');
Let Q9= FieldNumber('Quantity_Boxes_In_Pallet','Order_line');
Let Q10= FieldNumber('Quantity_Layers_In_Pallet','Order_line');
IF $(Q1) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Units_In_Pallet
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q2) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Units_In_Layer
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q3) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Units_In_Box
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q4) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Units_In_Inner
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q5) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Inners_In_Box
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q6) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Inners_In_Layer
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q7) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Inners_In_Pallet
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q8) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Boxes_In_Layer
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q9) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Boxes_In_Pallet
Resident Item_Master_Data;
ELSE
ENDIF;
IF $(Q10) = 0 then
Inner join(Order_line)
LOAD
SKU,
'0' AS Quantity_Layers_In_Pallet
Resident Item_Master_Data;
ELSE
ENDIF;