topic how to convert rows into columns in QlikView

how to convert rows into columns

Mon, 19 Mar 2012 10:26:29 GMT

Hello All,

I have some data in excel as below

Date

Time

Open

High

Low

Close Higher1

HClose1

HHigh1

HLow1

LClose1

LHigh1

LLow1

Close Higher2

HClose2

HHigh2

HLow2

LClose2

LHigh2

LLow2

Close Higher3

HClose3

HHigh3

HLow3

LClose3

LHigh3

LLow3

Close Higher4

HClose4

HHigh4

HLow4

LClose4

LHigh4

LLow4

Close Higher5

HClose5

HHigh5

HLow5

LClose5

LHigh5

LLow5

2008.12.03

14:00

1.262

1.2643

1.26

1.2628

0.00010

0.00240

-0.00120

FALSE

0.00590

0.00600

-0.00150

FALSE

0.00420

0.00630

0.00280

FALSE

0.00370

0.00520

0.00050

FALSE

0.00270

0.00430

0.00180

FALSE

Date

Time

Open

High

Low

Close Higher1

HClose1

HHigh1

HLow1

LClose1

LHigh1

LLow1

Close Higher2

HClose2

HHigh2

HLow2

LClose2

LHigh2

LLow2

Close Higher3

HClose3

HHigh3

HLow3

LClose3

LHigh3

LLow3

Close Higher4

HClose4

HHigh4

HLow4

LClose4

LHigh4

LLow4

Close Higher5

HClose5

HHigh5

HLow5

LClose5

LHigh5

LLow5

2008.12.03

14:00

1.262

1.2643

1.26

1.2628

0.00010

0.00240

-0.00120

FALSE

0.00590

0.00600

-0.00150

FALSE

0.00420

0.00630

0.00280

FALSE

0.00370

0.00520

0.00050

FALSE

0.00270

0.00430

0.00180

FALSE

Now while loading this data through loadscript, I want to convert Close Higher1 to LLow1 into a new column Bar1, (Close Higher2.........LLow2) into Bar 2 and so on. I know how to covert data from a specific column till end into a single column, but i am not sure how to covert portion of row into a column. Can someone please help me.

Thanks

Arif

how to convert rows into columns

jvitantonio — Mon, 19 Mar 2012 10:28:01 GMT

Try the load statement from file (in Edit Script) and play with the funcionalities

how to convert rows into columns

Mon, 19 Mar 2012 12:17:10 GMT

I have tried the cross tab while loading excel but it converts all the remaining rows except qualifiers into one single column. I dont want them in one single column. i want that after first four qualifier columsn every five columns be converted into single column as i mentioned above

Thanks

Arif

how to convert rows into columns

chriscammers — Mon, 19 Mar 2012 13:22:01 GMT

Arif,

Could you show what you want the data to look like. I am thinking you have to use a combination of joins and cross table but I'm not clear on what you are looking to see.

Chris

Re: how to convert rows into columns

Mon, 19 Mar 2012 13:45:57 GMT

Hello Chris,

I want the data to look like this

Date	Time	Open	High	Low	Close	Bar1	Bar2	Bar3	Bar4	Bar5
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	1	1	1	1	1
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	0.00010	0.00590	0.00420	0.00370	0.00270
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	0.00240	0.00600	0.00630	0.00520	0.00430
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	-0.00120	-0.00150	0.00280	0.00050	0.00180
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	FALSE	FALSE	FALSE	FALSE	FALSE
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	FALSE	FALSE	FALSE	FALSE	FALSE
2008.12.03	14:00	1.262	1.2643	1.26	1.2628	FALSE	FALSE	FALSE	FALSE	FALSE

After the first six columns, all the columns with postfix 1 (Close Higher1,HClose1,HHigh1,HLow1,LClose1,LHigh1,LLow1) are under bar1 and (Close Higher2,HClose2,HHigh2,HLow2,LClose2,LHigh2,LLow2) are under bar2 and so on. I hope this is clear now. let me know if i need to explain it some other way

Arif

Re: how to convert rows into columns

matteo_mi — Sun, 11 Nov 2012 11:31:51 GMT

You should use concatenate load

Re: how to convert rows into columns

Sun, 11 Nov 2012 16:35:25 GMT

Edit: Updated

Hi,

You have to split the table and load it multiple times to achieve this.

You can try using:

Crosstable(PostFixBar1, PostFix1_Values, 6)

Load

Date,

Time,

Open,

High,

Low,

Close Higher1,

HClose1,

HHigh1,

HLow1,

LClose1,

LHigh1,

LLow1

From

SourceFileName;

Crosstable(PostFixBar2, PostFix2_Values, 1)

Load

Date ,

[Close Higher2],

HClose2,

HHigh2,

HLow2,

LClose2,

LHigh2,

LLow2

From SourceFileName;

Crosstable(PostFixBar3, PostFix3_Values, 1)

Load

Date,

Close Higher3,

HClose3,

High3,

HLow3,

LClose3,

LHigh,

LLow3

From SourceFileName;

.........

............

.................and so on.

You could use the same procedure to transform all the columns in a similar way.

Hope that helps.

-Khaled