topic Re: Check if field is exist if no create '0' as missingfieldname in QlikView

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

seWork — Mon, 09 Jul 2018 08:50:28 GMT

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

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

olivierrobin — Mon, 09 Jul 2018 09:38:09 GMT

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

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

seWork — Mon, 09 Jul 2018 09:48:01 GMT

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

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

olivierrobin — Mon, 09 Jul 2018 09:49:41 GMT

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

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

seWork — Mon, 09 Jul 2018 10:39:02 GMT

Could you please explain how should I use it?

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

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

pascos88 — Mon, 09 Jul 2018 11:06:00 GMT

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,

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

olivierrobin — Mon, 09 Jul 2018 11:48:19 GMT

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

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

seWork — Mon, 09 Jul 2018 12:16:56 GMT

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;