Data Prep Append Tables

Janet_21505
Janet_21505 Altair Community Member
edited July 2020 in Community Q&A
​It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.  

I can append in the export utility, if not as a table inside of Data Prep.

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
Tagged:

Best Answer

  • Steve_Caiels
    Steve_Caiels
    Altair Employee
    edited July 2020 Answer ✓

    Hi Janet,

    The "Match Column on order" from the context menu is dependent on column type, but you have complete control via the Append Helper.

    Table 1 (Text, Date, Numeric)

    image

    Table 2 (Date, Numeric, Text)

    image

    The quickest way is to use the right Click "Match Columns on order", then edit the append.

    By default, the final column type will be based on the first table in the append, so this is unlikely to work straight away.

    image

    You can't fix it by changing the column type directly in the table, because the data has already been destroyed by the Append. But you can edit the Append and change the output column. Click the drop down or right click on the Appended table and select Edit Append, then choose your column types (usually Text if you want to store all 'data types'.)

    image

    This results in the following table

    image

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "

Answers

  • Steve_Caiels
    Steve_Caiels
    Altair Employee
    edited July 2020 Answer ✓

    Hi Janet,

    The "Match Column on order" from the context menu is dependent on column type, but you have complete control via the Append Helper.

    Table 1 (Text, Date, Numeric)

    image

    Table 2 (Date, Numeric, Text)

    image

    The quickest way is to use the right Click "Match Columns on order", then edit the append.

    By default, the final column type will be based on the first table in the append, so this is unlikely to work straight away.

    image

    You can't fix it by changing the column type directly in the table, because the data has already been destroyed by the Append. But you can edit the Append and change the output column. Click the drop down or right click on the Appended table and select Edit Append, then choose your column types (usually Text if you want to store all 'data types'.)

    image

    This results in the following table

    image

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "
  • CPorthouse
    CPorthouse
    Altair Employee
    edited July 2020
    Steve Caiels_21881 said:

    Hi Janet,

    The "Match Column on order" from the context menu is dependent on column type, but you have complete control via the Append Helper.

    Table 1 (Text, Date, Numeric)

    image

    Table 2 (Date, Numeric, Text)

    image

    The quickest way is to use the right Click "Match Columns on order", then edit the append.

    By default, the final column type will be based on the first table in the append, so this is unlikely to work straight away.

    image

    You can't fix it by changing the column type directly in the table, because the data has already been destroyed by the Append. But you can edit the Append and change the output column. Click the drop down or right click on the Appended table and select Edit Append, then choose your column types (usually Text if you want to store all 'data types'.)

    image

    This results in the following table

    image

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "
    Just to piggy back on Steve's answer.  If you click on the Combine button first and then drag the two tables you want to append together:
    image

    Click on the pencil icon on the append button which will open the append helper.  By default it will try to match the columns on name.  Change the radio button to "by Order" and then click on the Auto Match Columns.  You will get a warning that it will reset your current matches.  Click ok.
    image

    As you can see, I am matching two totally different tables with nothing in common.  You can then change the datatype in the output columns if necessary, or rematch columns if they are not correct.

    ------------------------------
    Chris Porthouse
    Senior Professional Services Consultant
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-10-2020 04:53 AM
    From: Steve Caiels
    Subject: Data Prep Append Tables

    Hi Janet,

    The "Match Column on order" from the context menu is dependent on column type, but you have complete control via the Append Helper.

    Table 1 (Text, Date, Numeric)

    image

    Table 2 (Date, Numeric, Text)

    image

    The quickest way is to use the right Click "Match Columns on order", then edit the append.

    By default, the final column type will be based on the first table in the append, so this is unlikely to work straight away.

    image

    You can't fix it by changing the column type directly in the table, because the data has already been destroyed by the Append. But you can edit the Append and change the output column. Click the drop down or right click on the Appended table and select Edit Append, then choose your column types (usually Text if you want to store all 'data types'.)

    image

    This results in the following table

    image

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------

    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "
  • Mahmoud
    Mahmoud
    Altair Employee
    edited July 2020
    Hi Janet,
    In DPS prepare page you can select all your tables in the order you need, then right click and select the Append helper.
    In the Append helper you can map each column.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "
  • Janet_21505
    Janet_21505 Altair Community Member
    edited July 2020
    Mahmoud said:

    Hi Janet,
    In DPS prepare page you can select all your tables in the order you need, then right click and select the Append helper.
    In the Append helper you can map each column.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "

    Thanks to all for the help.  You guys are the greatest. I tried to reply individually, but I was having troubles with getting my reply to send, so hoping replying to the group will work. 
    Steve- After studying your reply I did finally get it.  Thanks for all the pics as I am a visual learner.  I have a process in place that works. Thanks again!
    Chris - I was fighting with the Append Helper and with your and Steve's comments things did finally click. BTW I love your avatar!
    Mo - Thank for your follow up as well.  Now its on to Automator.  Stay tuned.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 07-10-2020 02:27 PM
    From: Mahmoud Abdolrahim
    Subject: Data Prep Append Tables

    Hi Janet,
    In DPS prepare page you can select all your tables in the order you need, then right click and select the Append helper.
    In the Append helper you can map each column.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------

    Original Message:
    Sent: 07-09-2020 12:54 PM
    From: Janet Grimsley
    Subject: Data Prep Append Tables

    It appears that Data Prep will only work with a strict append.  Just need to confirm as I am ingesting a txt file that has 3 types of information.  I am breaking it apart and need to arrange it differently.  The column names are not the same  for each of the 3 record types.  I want to ignore the column names and types.  The append will be based on the position of the column.

    I can append in the export utility, if not as a table inside of Data Prep.

    ------------------------------
    Janet Grimsley
    VP, Business Intelligence
    The Fauquier Bank
    Warrenton VA
    5403490215
    ------------------------------
    "

Categories