Check if field is exist if no create '0' as missingfieldname

[seWork]

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

[olivierrobin]

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

[seWork]

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

[olivierrobin]

in my script, value list is the list of fields you want to check

[seWork]

Could you please explain how should I use it?

How the "value list"  linked with the table 'Item_Master_Data'?

[pascos88]

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,

[olivierrobin · Accepted solution]

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

[seWork]

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;