Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Excluding columns from output

  1. #1

    Default Excluding columns from output

    Hi,
    I'm doing some monthly activity analysis whereby the total activity for any individual month may be 0. If and when this happens I want to be able to exclude the monthly data from my final output leaving only the months where relevant information exists.

    Is this simple to achieve and how would I go about getting there through LAE?

    Thanks.

  2. #2
    Contributor
    Join Date
    Dec 2006
    Location
    Boston
    Posts
    485

    Default

    I'm not sure I follow...your question seems to imply dropping data, but the title talks about dropping columns. Can you provide a simple example of data before and after? It sounds like you just want to wire a split node to the output of your node, and do a split criteria of where month != 0, but I think you are asking a more complicated question. Is there a group of data, and you want to exlude the group? Do you need to pivot the data, otherwise you would be trying to drop columns? Not really sure, so I am sorry I can't be more help.

    If you can provide text describing the input and desired output, or attach a simple example graph, maybe with some sample data (populate a static data node), that would also be helpful, and we can work from that.

    Cheers
    Rich

  3. #3
    Contributor
    Join Date
    Jan 2007
    Location
    Boston
    Posts
    134

    Default

    Hello,

    If you can post an example of your data I can answer more precisely, but in general; yes.

    You can quite easily exclude records from your output based on the values of a given field. For example:

    emit * where ('FOO' > 0)

    is very basic BRAINScript that tells a filter node to emit every record (and every field for that record) where the field FOO is greater than zero. Else, the whole record is excluded. You could be even more explicit with IF /THEN statements. It depends on what you want.

    You can also use a Split node to directly filter out all records not meeting a specific criteria.

    If your data isn't so much a single field in a record, but the result of a whole set of data, then a very simple Aggregate node can perform a summing operation to determine if a monthly total is 0. This output can then be used to perform a simple JOIN operation against your data to remove all the records matching the empty month.

    If you can provide more detail on how your data is setup and what precisely you want to do, better examples could be given.

    Regards,
    Timon Koufopoulos
    MDA Support.

  4. #4

    Default

    I'm not talking about filtering the data rather excluding columns where the data is null/irrelevant.

    For example:
    Jan Feb Mar Apr
    1 0 6 0
    2 0 2 0
    3 0 3 0
    4 0 4 0
    5 0 7 1

    15 0 22 1

    From this example I would deem "Feb" as being irrelevant as there is no real output to report on and so my question is can I exclude column "Feb" from the output?

  5. #5
    Lavastorm Employee
    Join Date
    Sep 2009
    Location
    Boston, Massachusetts, USA
    Posts
    50

    Default Use Pivot!

    Dear Stunelson,

    What you have asked for is a perfect example of when to use the Pivot Nodes. The Pivot - Names to Data node moves the field names from the metadata into the data itself so you can do the type of data cleansing you described above. The Pivot - Data to Names node reverses the process so that you can return your processed data to the original layout.

    In the attached graph, I have included three examples in the horizontal colored boxes. The first two examples do exactly what you have asked: they analyze the data to remove any columns with only zero's. However, I would recommend the final example as the most desirable solution. The box on the right contains a simple example implemented twice. This right-hand example simply tries to total all the months. You'll notice how difficult it is to get the top node to work since you'll have to change the logic in the node itself based upon the input data. However, the second node works no matter what the input data is. I highly recommend this second approach.

    Hope that helps,

    Rocco
    Attached Files Attached Files

  6. #6

    Default

    Thanks Rocco - I'll take a look and try to apply the logic to my graph!!

  7. #7

    Default

    Hi,

    I've requirement to fetch only the few columns from the input excel file based on the another column (department). There are 2 header records in the file.

    Here is the sample data,

    Computer Mechanical
    Branch Name Cust Name Cust ID Cust Name Cust ID
    USA Steve 1003 Jon 1002
    China Nick 1022 Robert 1040
    Russia Kim 2003 Daniel 1055

    Expected output :

    I need the below output if the department name is Computer,

    Branch Name Cust Name Cust ID
    USA Steve 1003
    China Nick 1022
    Russia Kim 2003

    I need the below output if the department name is Mechanical,

    Branch Name Cust Name Cust ID
    USA Jon 1002
    China Robert 1040
    Russia Daniel 1055

    Thanks,
    Ar

  8. #8

    Default Excluding the columns

    Hi,

    I've requirement to fetch only the few columns from the input excel file based on the another column (department). There are 2 header records in the file. Please refer the attachment.

    Here is the sample data,

    Computer Mechanical
    Branch Name Cust Name Cust ID Cust Name Cust ID
    USA Steve 1003 Jon 1002
    China Nick 1022 Robert 1040
    Russia Kim 2003 Daniel 1055

    Expected output :

    I need the below output if the department name is Computer,

    Branch Name Cust Name Cust ID
    USA Steve 1003
    China Nick 1022
    Russia Kim 2003

    I need the below output if the department name is Mechanical,

    Branch Name Cust Name Cust ID
    USA Jon 1002
    China Robert 1040
    Russia Daniel 1055

    Thanks,
    ArSample_data.jpgSample_data.jpg

  9. #9
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

    If the Excel file is consistently in the format of your screenshot then something like the attached should work where you can use the WorkbookSpec options to pull out specific sets of data.

    WorkerNames.brg

    Excel.jpg

  10. #10

    Default

    Quote Originally Posted by gmullin View Post
    If the Excel file is consistently in the format of your screenshot then something like the attached should work where you can use the WorkbookSpec options to pull out specific sets of data.

    WorkerNames.brg

    Excel.jpg
    Hi Mullin,

    Thanks for your suggestion. I've given the sample data for department wise but I want to read only the required columns from the excel file based on the input parameter (month). It will not be consistent as there are chances that month data will not be available.

    I need only the first 5 columns from the excel if the input parameter value is Jan.

    Here is the actual file,

    Sum of Jan Balance Sum of Feb Balance Sum of Mar Balance
    Col1 Col2 Col3 Incoming Outgoing Incoming Outgoing Incoming Outgoing
    1022 1123357 Test1 9,169.49 0.00 9,370.47 0.00 9,370.47 0.00
    1022 11234341 Test2 1.14 0.00 0.49 0.00 0.49 0.00

    Thanks,
    Ar

Similar Threads

  1. Replies: 5
    Last Post: 02-16-2012, 12:32 AM
  2. Excluding fields by Wildcard and Pattern
    By ltolleson in forum Nodes
    Replies: 4
    Last Post: 02-18-2010, 03:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •