delete a column from workflow transition | Community
Skip to main content
P
parmeshwarr3905
Level 3
May 2, 2023
Question

delete a column from workflow transition

  • May 2, 2023
  • 4 replies
  • 2695 views

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!

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    4 replies

    david--garcia
    david--garcia
    Level 10
    May 2, 2023

    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

     

      P
      parmeshwarr3905Author
      Level 3
      May 2, 2023

      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
      david--garcia
      Level 10
      May 2, 2023

      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.

       

       

       

        ParthaSarathy
        Community Advisor
        ParthaSarathyCommunity Advisor
        Community Advisor
        May 2, 2023

        @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.

         

         ~  ParthaSarathy S~  Click here to join ADOBE CAMPAIGN USER GROUP for Quarterly In-person | Hybrid | Virtual Meetups
          P
          parmeshwarr3905Author
          Level 3
          May 2, 2023

          @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
          Community Advisor
          LakshmiPravallikaCommunity Advisor
          Community Advisor
          May 10, 2023

          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.

            P
            parmeshwarr3905Author
            Level 3
            May 10, 2023

            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
            Community Manager
            Sukrity_WadhwaCommunity Manager
            Community Manager
            June 7, 2023

            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!

            Sukrity Wadhwa
            Terms & ConditionsAccessibility statement

            Loading footer...