topic Re: Load if field exists in QlikView

Load if field exists

seWork — Mon, 02 Jul 2018 06:02:46 GMT

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

Re: Load if field exists

olivierrobin — Mon, 02 Jul 2018 06:57:46 GMT

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

Re: Load if field exists

seWork — Mon, 02 Jul 2018 07:01:20 GMT

to load the first row of which table?

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

Re: Load if field exists

big_dreams — Mon, 02 Jul 2018 07:06:29 GMT

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,

Re: Load if field exists

olivierrobin — Mon, 02 Jul 2018 07:07:07 GMT

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

Re: Load if field exists

seWork — Wed, 04 Jul 2018 07:45:02 GMT

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;

Re: Load if field exists

olivierrobin — Wed, 04 Jul 2018 07:49:20 GMT

hello

at first sight, that seems correct

which errors do you get ?

Re: Load if field exists

seWork — Wed, 04 Jul 2018 08:08:59 GMT

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

Re: Load if field exists

olivierrobin — Wed, 04 Jul 2018 08:17:58 GMT

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

in you drop field, use

drop field $(fnamequn)

Re: Load if field exists

seWork — Wed, 04 Jul 2018 08:25:15 GMT

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

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

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

Re: Load if field exists

olivierrobin — Wed, 04 Jul 2018 08:32:45 GMT

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

Re: Load if field exists

seWork — Wed, 04 Jul 2018 08:38:42 GMT

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

Re: Load if field exists

olivierrobin — Wed, 04 Jul 2018 08:44:21 GMT

in document properties, check generate log file

and so you can really view the different steps executed

Re: Load if field exists

seWork — Wed, 04 Jul 2018 08:45:20 GMT

"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

Re: Load if field exists

olivierrobin — Wed, 04 Jul 2018 08:51:14 GMT

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

Re: Load if field exists

seWork — Wed, 04 Jul 2018 08:57:05 GMT

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

Re: Load if field exists

olivierrobin — Wed, 04 Jul 2018 09:02:48 GMT

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)