How to generate a new column a new columns in workbook

Follow
Jee
  • Edited

Hi there,

 

I am pretty new to Datameer and I am learning now. I am trying to generate(add) new columns in worksheets, for instance, there are 2 columns (name, age) already there, now want to add 3rd column(Dept String).

Also, I am wondering how to generate uuid(of workbook) column for an existing sheet..

It might be trivial question, but I need this to know.

 

Thanks,

Kavitha

0

Comments

8 comments

Date Votes
  • Konsta Danyliuk

    Hello Kavitha,
    Thank you for contacting Datameer Community.

    Perhaps you could provide more details on the goal you are trying to achieve, e.g. how your initial dataset looks like, what data transformation you need to perform?

    In general, in Datameer you can't manually add a column and fill it in with the data which is absent in your source dataset.

    For example, in case you work with a dataset ingested from csv file and this dataset contains Name and Age information only, you can't add Dept data directly from Datameer. You would need to load information about Dept into Datameer first. It might be another csv file that should be inserted into Datameer. As soon as both datasets are loaded, you could join Dept data with Name and Age in a Workbook using Join instrument.

    0
    Comment actions Permalink
  • Jee

    Hi Konsta ,

    Thanks for your reply.

    How to generate uuid(ofworkbook) ?

    For example, I have columns name and age. Now I want to add an another column UUID. I know the value is going to be same for all the rows. Because, we need to upload this data to external database.

     

    0
    Comment actions Permalink
  • Konsta Danyliuk

    Hello Kavitha,
    Just not sure if I got your request property, could clarify it a bit more, please?

    As far as I understand, currently you have a Sheet in a Workbook that looks like below.


     What exactly do you need to add in Column C - should it be the same value (Number or Letter) for every record, should to be record unique number or unique string?

    0
    Comment actions Permalink
  • Alan Mark

    Hi Kavitha,

    I think you need the following formula:

    IF(ISNULL(#Sheet_Name!Column_Name);"";"value")

    Please change Sheet_Name to match the name of the sheet with your name column, and make Column_Name match the name of your column.

    Then, simply change value to the static value you want to appear on all records.

     

    0
    Comment actions Permalink
  • Jee

    Hi,

    These are 9 column's in the Target table,

    1. IDP_WAREHOUSE_ID
    2. IDP_AUDIT_ID
    3. IDP_DATA_DATE
    4. DM_OBJ_VERSION
    5. DM_WORKBOOK_NAME
    6. ACTIVITY_TYPE
    7. ACTIVITY_PURPOSE
    8. DESCRIPTION
    9. ACTIVITY_ID

     

    But the source data(the input file for datameer Workbook) contains only 4 columns(from 6 - 9), so I need to generate the remaining columns(the first 5) in order to map each column to the respective column in target table while uploading the data.

     

    1. For the column IDP_WAREHOUSE_ID, I have been asked to generate the value of WorkBook(UUID)(dont bother about the value of IDP_WAREHOUSE_ID in the table, Because we will truncate the existing rows and upload the new rows)

    2. I need to add the IDP_AUDIT_ID column(the value is 20161025, it  is like constant). Similarly I need to add the rest of the columns as well.

    0
    Comment actions Permalink
  • Alan Mark

    Kavitha,

    Here is an example specific to the table you show:

    IF(ISNULL(#Sheet_Name!ACTIVITY_TYPE);"";"20161025")

    You just need to change "Sheet_Name" to be the name of the sheet in your workbook.  

    This will make a column where the value 20161025 is on every record.

    As far as the UUID is concerned, you can get the value for this with the REST API to read the workbook JSON, and you can view the UUID in this response.

    REST API Workbook - Read Workbook

    Then you can create another new column, but use the same formula:

    IF(ISNULL(#Sheet_Name!ACTIVITY_TYPE);"";"UUID")

    Where you replace UUID with the value from the REST API response.

    The logic behind this is we know ACTIVITY_TYPE will always have a value.  So, we test if it is null.  This will always fail, so then we say in the event of boolean false, apply the static value.

    0
    Comment actions Permalink
  • Thyagarajan Sudhakaran

    Hi,

    I am not sure if i executed this correctly.. However below is my data & when i try to apply the below formula it doesn't resolve to static value. I mean the the column "ALERTID" doesn't change his value from null to 20161025..  FYI.. null_check is my sheet name

    IF(ISNULL(#null_check!ALERTID);"";"20161025")

     

    1
    Comment actions Permalink
  • Joel Stewart

    Thyagarajan, in the advanced filter dialog the function in the screenshot indicates output of a string function. In the Advanced Filter, the expected output should be a Boolean type only. If you'd like to transform the NULL data into a String, please use the same function from the screenshot directly in the workbook. 

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post