I have to create a dataset based on number of column

[Sshasnk]

I have a dataset where I have to pick the once where number of columns matches with the first row column rest should be ignored 

Example

Fund	account	amount	purchase	sell	buy
ABC	123	489	562	58	456
NLM	154	456	314	54	23
BC	145	562	23	156	489
Fund	account	amount	purchase	sell	buy
ABC	1456	789	456	254	623
GHL	48	145	56	23	45
Fund	account	amount	purchase	sell	buy
HLK	1546	231	235	15
Fund	account	amount
LMN	145	789

1. the First row has 6 columns and so as 6th row

2. While the last-second record does not have the same number of columns as the first row so we don't have to pick

Output:

Fund	account	amount	purchase	sell	buy
ABC	123	489	562	58	456
NLM	154	456	314	54	23
BC	145	562	23	156	489
ABC	1456	789	456	254	623
GHL	48	145	56	23	45
HLK	1546	231	235	15

[wdavis]

Hi @Sshasnk 

I've attached a workflow which should help with this, you might want to tweak to your exact requirements.

• Transpose data so it's all in columns, you can the do a count of the number of fields that aren't blank
• You can then set a filter to the number of fields should contain data (I've set this to 6)
  • This might need to be updated to 5 if you want to include the 'HLK' fund as it's missing data for 'Buy'
• Use the Cross-Tab to re-format to tabular format
• Final steps are to format table and clean up

Hopefully this makes sense and works for you!

Will