Dropping Fields from a table present in other table

[ferha_jafri]

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

[cmorri1988 · Accepted solution]

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;

[vamsee]

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;

[ferha_jafri]

Thanks