topic Re: convert many columns to be one column in QlikView

convert many columns to be one column

Wed, 16 Apr 2014 06:25:59 GMT

Dears,

Appreciate your help.

I have a file with this data structure

AccNo Name Invoice#1 Invoice#2 Invocie#3

1 john 112345 112346

2 michael 112395 112398 112399

I want to import it and be like this (convert many columns to be one column)

AccNo Name Invoice#

1 john 112345

1 john 112346

2 michael 112395

2 michael 112398

2 michael 112399

Re: convert many columns to be one column

alexandros17 — Wed, 16 Apr 2014 06:31:17 GMT

here it is:

TAB:

LOAD * Inline [

AccNo , Name, Invoice#1, Invoice#2 , Invoice#3

1 , john , 112345, 112346,

2 , michael , 112395 , 112398 , 112399

];

FinalTab:

NoConcatenate

LOAD AccNo , Name, Invoice#1 as invoice Resident TAB;

Concatenate

LOAD AccNo , Name, Invoice#2 as invoice Resident TAB;

Concatenate

LOAD AccNo , Name, Invoice#3 as invoice Resident TAB;

DROP Table TAB;

Re: convert many columns to be one column

Anonymous — Wed, 16 Apr 2014 06:34:13 GMT

PFA.

Hope it helps you

Regards

Nitin

Re: convert many columns to be one column

MK_QSL — Wed, 16 Apr 2014 06:48:03 GMT

CrossTable(Invoice, InvoiceNumber,2)

Load * Inline

[

AccNo, Name, Invoice#1, Invoice#2, Invoice#3

1, john, 112345, 112346,

2, michael, 112395, 112398, 112399

];

Drop Field Invoice;

Re: convert many columns to be one column

rajat2392 — Wed, 16 Apr 2014 06:55:43 GMT

you can simply use crosstable...

when you load the data, click next, Enable Transformation Step, then next and then cross table..set first two fields (AccNo., Name) as qualifiers and Give attribute a name as "Invoice"

Re: convert many columns to be one column

Wed, 16 Apr 2014 06:57:12 GMT

use crosstable prefix.

crosstable(invoice#, invoice, 2)

Load * Inline

[

AccNo, Name, Invoice#1, Invoice#2, Invoice#3

1, john, 112345, 112346,

2, michael, 112395, 112398, 112399

];

it will combine all the invoice fields into one field invoice

Re: convert many columns to be one column

arjunkrishnan — Wed, 16 Apr 2014 07:07:51 GMT

D1:

LOAD AccNo,

[Name ],

Invoice#1,

Invoice#2,

Invoice#3

FROM

inVO.xls

(biff, embedded labels, table is [Sheet1$]);

NoConcatenate

T1:

LOAD AccNo,

[Name ],

Invoice#1 AS Invoice#

Resident D1;

Concatenate

T2:

LOAD AccNo,

[Name ],

Invoice#2 AS Invoice#

Resident D1;

Concatenate

T3:

LOAD AccNo,

[Name ],

Invoice#3 AS Invoice#

Resident D1;

RESULT LIKE THAT

Re: convert many columns to be one column

hic — Wed, 16 Apr 2014 07:08:27 GMT

Use the Crosstable prefix. Read more here: http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

HIC