LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stunelson

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.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

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.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stunelson

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?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rpigneri

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
Attachments:
columnRemover.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stunelson

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gmullin

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

Originally posted by: gmullin
					

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

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

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,
ArSample_data.jpg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

Originally posted by: Arunn
					

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,

ArSample_data.jpg

			

Hi,

Can someone please help me to read the above file ?

Thanks

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gmullin

Attached is a couple of different ways using the data you found. There's always multiple ways to get the result you want, with these examples you should be able to work out how to cater for the data as you are expecting to receive it.

Months.jpg
MonthsExcel.brg