column to rows

[raadwiptec]

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.

[Colin-Albert]

Use a CROSSTABLE load.

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

[raadwiptec]

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'

[sunny_talwar]

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

[avinashelite]

check the attachment for the soultion

[raadwiptec]

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]

[raadwiptec]

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

[avinashelite]

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?

[sunny_talwar]

May be this:

Table:

CrossTable (YearMonth, Value, 3)

LOAD A as [Customer id],

    B as [Product number],

    C as prices,

    //D,

    E,

    F,

    G,

    H,

    I,

    J

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;

[raadwiptec]

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]