How to format the rows?

[Anonymous]

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

[Anonymous]

Hi,

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

[Anonymous]

Hi,

I have attached the Sample data

Thanks,

Vishnu

[#4]

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]

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

[vinieme12]

vvrchilukoori‌ like this? added CSUIP next to Description

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

Hi Vineeth,

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

Thanks,

Vishnu

[vinieme12]

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.