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.

[raadwiptec]

hi sunny,

the result is showing all 2016?

[sunny_talwar]

Oops made a small error:

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('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;

[raadwiptec]

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

[sunny_talwar]

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

[raadwiptec]

Hi sunny,

Attached

[sunny_talwar]

Try this code:

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 (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;

[raadwiptec]

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