Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I have a requirement in which I have a list of field which needs to be drop from all the Qlikview Applications , as I am attaching a sample in which Removecolumns is a table which have a list of fields needs to be drop from all the applications. And MainTable is the table from which we want to remove the fields relevant to the MainTable as Removecolumns table have some extra fields as well.
Thanks in advance
Ferha
Hi Ferha,
The following code will identify fields which exist in 'TABLE_1' and 'TABLE_2', and delete them from TABLE 2.
Please replace to reflect your table names
Hope this helps
---------------------
//CREATE LIST OF FIELDS IN TABLE 1
FOR fieldNo = 1 to NoOfFields('TABLE_1')
let _fieldName = fieldName($(fieldNo), 'TABLE_1');
TABLE_1_FIELDS:
LOAD
'$(_fieldName)' as TABLE1_FIELD_NAME
autogenerate 1;
next
//CREATE LIST OF FIELDS IN TABLE 2
FOR fieldNo = 1 to NoOfFields('TABLE_2')
let _fieldName = fieldName($(fieldNo), 'TABLE_2');
TABLE_2_FIELDS:
LOAD
'$(_fieldName)' as TABLE2_FIELD_NAME
autogenerate 1;
next//CREATE LIST OF FIELDS IN BOTH TABLES
FIELD_NAMES:
LOAD
TABLE2_FIELD_NAME AS FIELD_NAME
Resident TABLE_2_FIELDS
WHERE EXISTS(TABLE1_FIELD_NAME,TABLE2_FIELD_NAME);
//DROP FIELD FROM TABLE 2 WHICH OCCURS IN BOTH TABLES
let noRows = NoOfRows('FIELD_NAMES')-1;
for i=0 to $(noRows)
let vVar= peek('FIELD_NAME',$(i),'FIELD_NAMES');
drop field '$(vVar)' from TABLE_2;next i
DROP TABLES TABLE_1_FIELDS, TABLE_2_FIELDS, FIELD_NAMES;
Hi Ferha,
The following code will identify fields which exist in 'TABLE_1' and 'TABLE_2', and delete them from TABLE 2.
Please replace to reflect your table names
Hope this helps
---------------------
//CREATE LIST OF FIELDS IN TABLE 1
FOR fieldNo = 1 to NoOfFields('TABLE_1')
let _fieldName = fieldName($(fieldNo), 'TABLE_1');
TABLE_1_FIELDS:
LOAD
'$(_fieldName)' as TABLE1_FIELD_NAME
autogenerate 1;
next
//CREATE LIST OF FIELDS IN TABLE 2
FOR fieldNo = 1 to NoOfFields('TABLE_2')
let _fieldName = fieldName($(fieldNo), 'TABLE_2');
TABLE_2_FIELDS:
LOAD
'$(_fieldName)' as TABLE2_FIELD_NAME
autogenerate 1;
next//CREATE LIST OF FIELDS IN BOTH TABLES
FIELD_NAMES:
LOAD
TABLE2_FIELD_NAME AS FIELD_NAME
Resident TABLE_2_FIELDS
WHERE EXISTS(TABLE1_FIELD_NAME,TABLE2_FIELD_NAME);
//DROP FIELD FROM TABLE 2 WHICH OCCURS IN BOTH TABLES
let noRows = NoOfRows('FIELD_NAMES')-1;
for i=0 to $(noRows)
let vVar= peek('FIELD_NAME',$(i),'FIELD_NAMES');
drop field '$(vVar)' from TABLE_2;next i
DROP TABLES TABLE_1_FIELDS, TABLE_2_FIELDS, FIELD_NAMES;
May be try
MainTable:
LOAD * INLINE [
ProdictID, Product, Shape, Quantity, Price, Order, OrderDetail, SalePerson
1, Ball, Round, 2, 10, O1, 2, Kapil
1, Ball, Round, 3, 10, O2, 3, Roushan
1, Ball, Round, 4, 10, O3, 4, Sudeep
2, Bat, Rectangle, 1, 10, O3, 2, Kapil
2, Bat, Rectangle, 2, 10, O4, 3, Roushan
2, Bat, Rectangle, 3, 10, O5, 4, Deepak
];
RemoveColumns:
LOAD
*
INLINE [
Columns
Quantity
SalePerson
OrderDetail
SaleId
Country
];
LET i =1;
SET ErrorMode=0; /** Ignores errors **/
FOR i= 1 to NoofRows('RemoveColumns')
Let vColumnName = Peek('Columns', i-1, 'RemoveColumns');
Drop Field [$(vColumnName)];
NEXT i;
SET ErrorMode=1; /** Setting back to default **/
DROP Table RemoveColumns;
Thanks