Load if field exists

[seWork]

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

[olivierrobin]

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

[seWork]

to load the first row of which table?

Each script part in the script above contain different calcs how it will work?

[big_dreams]

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,

[olivierrobin]

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

[seWork]

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;

[olivierrobin]

hello

at first sight, that seems correct

which errors do you get ?

[seWork]

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

[olivierrobin]

as your field names contains spaces, don't forget to enclose them in []

in you drop field, use

drop field $(fnamequn)

[seWork]

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