Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
seWork
Contributor III
Contributor III

Load if field exists

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



16 Replies
olivierrobin
Specialist III
Specialist III

Total Units not in pallet is created by the previous load . Did it execute ?

for you field not dropped, as it is created in the load where you have the error, it has been crated, so it can't be deleted

seWork
Contributor III
Contributor III
Author

I edited the previous post and added the "Script execution log"


regarding the drop

I left join does fields with resident table for the calculation

there are 5 "Quantity_Units*" fields the in both cases i'm using a loop(For locating them in the resident table and left join them to the destination table and for locating them in the destination table and dropping them from it)

the only field that been left joined successfully but doesn't be dropped is the " Quantity_Units_In_Layer " field

olivierrobin
Specialist III
Specialist III

in document properties, check generate log file

and so you can really view the different steps executed

seWork
Contributor III
Contributor III
Author

"Total Units not in pallet"  is supposed to be created by the previous load but when the script run end the table structure contain it even that I'm getting the error this is the reason that I suspect that the error caused by loading order

olivierrobin
Specialist III
Specialist III

did you generate the log and sees whatt's in it ?

seWork
Contributor III
Contributor III
Author

Yes, as I suspected as a result of the "else if" the "Quantity_Units_In_Layer" part been loaded before the Quantity_Units_In_Pallet


I think that I will try to separate them with different loops

unless you will recommend for other solution

olivierrobin
Specialist III
Specialist III

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)