topic Re: How to format the rows? in QlikView

How to format the rows?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to format the rows?

Anonymous — Wed, 08 Feb 2017 07:45:02 GMT

Hi,

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

Re: How to format the rows?

Anonymous — Wed, 08 Feb 2017 08:07:00 GMT

Hi,

I have attached the Sample data

Thanks,

Vishnu

Re: How to format the rows?

Wed, 08 Feb 2017 09:14:48 GMT

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.

Re: How to format the rows?

vinieme12 — Wed, 08 Feb 2017 09:52:33 GMT

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

Re: How to format the rows?

vinieme12 — Wed, 08 Feb 2017 10:11:46 GMT

vvrchilukoori‌ like this? added CSUIP next to Description

Re: How to format the rows?

Anonymous — Wed, 08 Feb 2017 16:56:58 GMT

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

Re: How to format the rows?

vinieme12 — Wed, 08 Feb 2017 18:54:44 GMT

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

Re: How to format the rows?

Anonymous — Wed, 08 Feb 2017 23:16:15 GMT

Hi Vineeth,

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

Thanks,

Vishnu

Re: How to format the rows?

vinieme12 — Thu, 09 Feb 2017 06:30:49 GMT

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.

Re: How to format the rows?

vinieme12 — Thu, 09 Feb 2017 07:19:27 GMT

Can you mark a response as correct and close the thread please