delete a column from workflow transition

[parmeshwarr3905]

Hi everyone,

I'm importing data from a file into campaign list. I'm collecting a file from SFTP using file collector activity, after that I used Data loading (file) activity in workflow. I want to remove a column if it is empty and pass the remaining fields to next activity. Is it possible to remove column from workflow transition using JavaScript? let me know of any other alternatives.

Really appreciate your inputs on this. Thank you!

[david--garcia]

You can, if you know the physical name of the temp table using (vars.tableName) which should give something like wkf29660147_399_1

Then use sqlExec to execute raw sql and remove the column.

sqlExec('ALTER TABLE '+vars.tableName+' DROP COLUMN yourcol');

To get the name of your column, right click on the transition, display target --> configure list --> generated SQL query

Then you can see the names of your columns, i.e. (sFirstName) when dropping the column, no need to use the Aliases i.e. W0, R1 just the name of the column.

SELECT   W0.iId, W0.iCmOneID, W0.sAccount, COALESCE(R1.sLastName, '') || E' ' || COALESCE(R1.sFirstName, '') || E' (' || COALESCE(R1.sEmail, '') || E')' FROM wkf29660147_409_1 W0 JOIN NmsRecipient R1 ON (R1.iRecipientId = W0.iId) LIMIT 0

[parmeshwarr3905]

Thank you @david--garcia for your quick response. I tried this approach in JS activity. JS activity get executed and the next activity which is connected to JS activity throws an error.

PGS-220000 PostgreSQL error: ERROR: column "spid" does not exist LINE 1: ...sPId) SELECT iLineNum,sFirstName,sLastName,sEmail,sPId FROM ... ^ HINT: There is a column named "spid" in table "grp144106150", but it cannot be referenced from this part of the query. .

[david--garcia]

It doesn exist because you've just deleted it, however, you have an activity after it still referencing it.

In this case, you still need to loop all records and check if any of them contain a value for your column before you execute the column removal. something like below.

var query = xtk.queryDef.create(      
  <queryDef schema={vars.targetSchema} operation="select">      
    <select>      
      <node expr="@id"/>    
      <node expr="@pId"/> 
    </select>        
  </queryDef>      
);      
var results = query.ExecuteQuery();

var counter = 0;
for each(var i in results) {
	if (i.@pId){counter++}//increase counter if value in column for each record
}

if (counter < 1) {//drops column if no value in any record
	sqlExec('ALTER TABLE '+vars.tableName+' DROP COLUMN sPId');
}

Probably worth also posting a screenshow of your workflow, and the configuration of the activity after your JS.

[parmeshwarr3905]

@david--garcia I have placed List Update activity after JS activity to create the lists with the fields/columns which are not empty. In above code since we are trying to remove column with same command only after checking the counter I think it would throw same error again. 

Thanks.

[ParthaSarathy]

@parmeshwarr3905 , Manually, you can unselect the column in data Loading activity.

Data Loading > Click here to change the file format > select 'Ignore Column' on the column you want to remove.

In the below screenshot, field_3 column will be removed from temp table.

[parmeshwarr3905]

@ParthaSarathy I need to unselect the column in case it is empty from an incoming file because some time that column contains values and I have to push it to list. Could you please suggest the approach for the same.

[LakshmiPravallika]

Hi @parmeshwarr3905 

Even though you have tried to delete the column from the Transition. ultimately that particular column will be present in the List if the List gets reappended for the next run.It will default be updated to Blank even if data do not exists in the Transition (or) exists with Blank.

Regards,

Pravallika.

[parmeshwarr3905]

Thank you @LakshmiPravallika   for your response.

is there any other way like to delete a field/column directly from list if it's empty?

[Sukrity_Wadhwa]

Hi @parmeshwarr3905,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!