Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to format the rows?

Hi,

I want the rows(Even rows) that contain 'CUSIP:XXXXX' to be in a new column side by to the odd rows. Ex: CUSIP no. of 2nd row with row-1 in new column. How can I do that? and I want to remove only the value(not entire row or column )in column-1&row no.28 'CL A'.

Thanks,

Vishnu

Capture.JPG

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

in the Existing script during FACT: table load just add a where clause

FACT:.

.

.

.Resident

WHERE len(DATE)>0;

This is all that is needed

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

10 Replies
Anonymous
Not applicable
Author

Hi,

Upload sample data and a worked example of the expected output for the community to assist.

Anonymous
Not applicable
Author

Hi,

I have attached the Sample data

Thanks,

Vishnu

Capture.JPG

Not applicable
Author

Hi,

In your Script, please use the following lines just before the original load statement:

TestTable:

LOAD * where len(YourColumn1) >0;

load *, if(Mod(RecNum,2)=0,YourColumn3,'N.A.') as DerivedCol;

LOAD RecNo() as RecNum,*;

//LOAD Your Original Columns here;

That should work.

vinieme12
Champion III
Champion III

How exactly do you want the output to look like? your description is a bit confusing

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

vvrchilukoori‌ like this? added CSUIP next to Description

row_to_col.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Hi Vineeth,

Yes , the 1st 4 rows in description belong to same company Facebook. So I want make a separate column which contains CUSIP and remove the row that contains 'CL A' and 'UNSOLICITED'.

Example :

Old format:

Description

FACEBOOK INC

CL A

CUSIP:3030M102

UNSOLICITED

New Format:

Description             CUSIP

FACEBOOK INC    3030M102

Thanks,

Vishnu

vinieme12
Champion III
Champion III

in the Existing script during FACT: table load just add a where clause

FACT:.

.

.

.Resident

WHERE len(DATE)>0;

This is all that is needed

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Hi Vineeth,

It worked. Can you explain what you did in the script editor.

Thanks,

Vishnu

vinieme12
Champion III
Champion III

Sure,

First we create a groupkey, which identifies what rows of data are related.

We then use the groupkey to lookup cusip values using Applymap ()

Comment the load scripts for the different tables and reload  to see what is happening in each table load , it'll be easier to understand.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.