topic Re: column to rows in QlikView

column to rows

raadwiptec — Wed, 18 Nov 2015 10:03:45 GMT

Hi all,

This is my scenario.. attached in the excel sheet.

Here A,B,C.... is the actual excel columns and not the header columns.. but these columns are also important here as in the attachment

Customer id

Product number

prices

201508

201509

201510

201511

201512

201601

201602

basically I need to always start from the column 'E 'To transpose it to Rows

attached file.

Re: column to rows

Colin-Albert — Wed, 18 Nov 2015 10:09:02 GMT

Use a CROSSTABLE load.

https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 10:34:58 GMT

crosss table is not giving me the entire solution, The problem is it has to take the price calculation from certain column so here it is column 'E'

Re: column to rows

sunny_talwar — Wed, 18 Nov 2015 10:56:15 GMT

What is the logic behind excluding E? Because it has no price?

Re: column to rows

avinashelite — Wed, 18 Nov 2015 11:02:08 GMT

check the attachment for the soultion

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 12:21:43 GMT

hi Avinash,/sunny

I have already arrived at the first part of your solution earlier.

The solution is almost correct .. but here you have hardcoded in the load statement (here is where iam looking for a solution). that is the reason I want to include excel default columns - A,B,C,D..... etc

[201508],
[201509],
[201510],
[201511],
[201512],
[201601],
[201602]

Here Column E - would change monthly for example -column E -for this month is 201509 ''for the next month column E becomes 201510

it would be a decreasing - increasing chronological order

this month

201508],
[201509], - column E
[201510],
[201511],
[201512],
[201601],
[201602]

¦ next month

2015089,
[201510], - column E
[201511],
[201512],
[201601],
[201602],
[201603]

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 13:01:01 GMT

Hi sunny,

E is included and taken as a starting point .. Because E column would change the year and month like 201510, next month 201511. so my calculation should consider the E column for further report the prices

Re: column to rows

avinashelite — Wed, 18 Nov 2015 13:21:53 GMT

See in the crosstable function you will mention the starting point rite? in your terms E if we calculate it its 4 .

after convertion you will get all the date and month in one column so can play with that data rite?

Re: column to rows

sunny_talwar — Wed, 18 Nov 2015 13:27:57 GMT

May be this:

Table:

CrossTable (YearMonth, Value, 3)

LOAD A as [Customer id],

B as [Product number],

C as prices,

//D,

FROM

[Book1 (4).xlsx]

(ooxml, no labels, header is 2 lines, table is Sheet2);

Temp:

LOAD Max(YearMonthValue) as MaxYearMonth,

Min(YearMonthValue) as MinYearMonth;

LOAD Date#(Num([201509.000000], '00'), 'YYYYMM') as YearMonthValue

FROM

[Book1 (4).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet2, filters(

Transpose(),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

LET vMinYearMonth = Peek('MaxYearMonth');

NewTable:

NoConcatenate

LOAD [Customer id],

[Product number],

prices,

Date(Pick(Match(YearMonth, 'E', 'F', 'G', 'H', 'I', 'J'), $(vMinYearMonth), AddMonths($(vMinYearMonth), 1), AddMonths($(vMinYearMonth), 2)

, AddMonths($(vMinYearMonth), 3), AddMonths($(vMinYearMonth), 4), AddMonths($(vMinYearMonth), 5)), 'YYYYMM') as YearMonth,

Value

Resident Table;

DROP Tables Table, Temp;

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 13:37:24 GMT

Hi Avinash,

But E column is a dynamic one. it gets changed.. After 2 months 201508 would not exists in the source file..the script will fail

this month

201508],
[201509], - column E
[201510],
[201511],
[201512],
[201601],
[201602]

¦ next month

2015089,
[201510], - column E
[201511],
[201512],
[201601],
[201602],
[201603]

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 13:43:32 GMT

hi sunny,

the result is showing all 2016?

Re: column to rows

sunny_talwar — Wed, 18 Nov 2015 13:46:39 GMT

Oops made a small error:

Table:

CrossTable (YearMonth, Value, 3)

LOAD A as [Customer id],

B as [Product number],

C as prices,

//D,

FROM

[Book1 (4).xlsx]

(ooxml, no labels, header is 2 lines, table is Sheet2);

Temp:

LOAD Max(YearMonthValue) as MaxYearMonth,

Min(YearMonthValue) as MinYearMonth;

LOAD Date#(Num([201509.000000], '00'), 'YYYYMM') as YearMonthValue

FROM

[Book1 (4).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet2, filters(

Transpose(),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

LET vMinYearMonth = Peek('MaxYearMonthMinYearMonth');

NewTable:

NoConcatenate

LOAD [Customer id],

[Product number],

prices,

Date(Pick(Match(YearMonth, 'E', 'F', 'G', 'H', 'I', 'J'), $(vMinYearMonth), AddMonths($(vMinYearMonth), 1), AddMonths($(vMinYearMonth), 2)

, AddMonths($(vMinYearMonth), 3), AddMonths($(vMinYearMonth), 4), AddMonths($(vMinYearMonth), 5)), 'YYYYMM') as YearMonth,

Value

Resident Table;

DROP Tables Table, Temp;

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 14:06:56 GMT

Hi sunny ,

iam getting erroed for this part

LOAD Date#(Num([201509.000000], '00'), 'YYYYMM') as YearMonthValue

..actually my period column is like this "LKP-201510" not as 2015090000

Re: column to rows

sunny_talwar — Wed, 18 Nov 2015 14:14:33 GMT

Can you share the labels from your actual database and how you want to see them after the transformation?

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 14:27:09 GMT

Hi sunny,

Attached

Re: column to rows

sunny_talwar — Wed, 18 Nov 2015 14:40:30 GMT

Try this code:

Table:

CrossTable (YearMonth, Value, 3)

LOAD A as [Customer id],

B as [Product number],

C as prices,

//D,

FROM

[Book1 (5).xlsx]

(ooxml, no labels, header is 2 lines, table is Sheet2);

Temp:

LOAD Max(YearMonthValue) as MaxYearMonth,

Min(YearMonthValue) as MinYearMonth;

LOAD Date#(Num(Right(@1, 6), '00'), 'YYYYMM') as YearMonthValue

FROM

[Book1 (5).xlsx]

(ooxml, no labels, header is 1 lines, table is Sheet2, filters(

Transpose(),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

LET vMinYearMonth = Peek('MinYearMonth');

NewTable:

LOAD *,

Year(YearMonth) as Year,

Num(Month(YearMonth)) as Month;

NoConcatenate

LOAD [Customer id],

[Product number],

prices,

Date(Pick(Match(YearMonth, 'E', 'F', 'G', 'H', 'I', 'J'), $(vMinYearMonth), AddMonths($(vMinYearMonth), 1), AddMonths($(vMinYearMonth), 2)

, AddMonths($(vMinYearMonth), 3), AddMonths($(vMinYearMonth), 4), AddMonths($(vMinYearMonth), 5)), 'YYYYMM') as YearMonth,

Value

Resident Table;

DROP Tables Table, Temp;

Re: column to rows

raadwiptec — Wed, 18 Nov 2015 15:18:47 GMT

Hi Sunny,

Iam getting months only for 1,8,9,10,11,12 strange..

Can u explain me in lay words.. just the parts where functions ar used.. so I can correct it then

Temp:

LOAD Max(YearMonthValue) as MaxYearMonth,

Min(YearMonthValue) as MinYearMonth;

LOAD Date#(Num([201509.000000], '00'), 'YYYYMM') as YearMonthValue

FROM

[Book1 (4).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet2, filters(

Transpose(),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

LET vMinYearMonth = Peek('MaxYearMonthMinYearMonth');

NewTable:

NoConcatenate

LOAD [Customer id],

[Product number],

prices,

Date(Pick(Match(YearMonth, 'E', 'F', 'G', 'H', 'I', 'J'), $(vMinYearMonth), AddMonths($(vMinYearMonth), 1), AddMonths($(vMinYearMonth), 2)

, AddMonths($(vMinYearMonth), 3), AddMonths($(vMinYearMonth), 4), AddMonths($(vMinYearMonth), 5)), 'YYYYMM') as YearMonth,

Value