Load Row as Column Value?

[Anonymous]

This is a loading question. An example of my data set is below. I want to load this to read the months at the top as 1 field called Month. That way, users can  see the value of their accounts over a month span.

i.e Acct 5678 sum is 600 between Jan and Mar.

[sunny_talwar]

Use cross table

You may find this blog useful: The Crosstable Load

HTH

Best,

Sunny

[MK_QSL]

You need to use CrossTable function. Provide your sample data in excel file to provide you script code...

[buzzy996]

try,

just load ur data and use cross table---u can play here rows as columns &vice-versa...

[Anonymous]

Manish - Cant figure out how to upload a file. Data simply looks like below ...

[Anonymous]

Sunny - This was very helpful. However, I have more than one qualifier field so I am struggling to understand how I make those qualifying fields known.

[MK_QSL · Accepted solution]

Try below

CrossTable(Month,Value,4)

Load AC1, AC2, AC3, AC4,Jan,Feb,Mar.........and so on from your FIleName;

[sunny_talwar]

You can do something like this:

Table:

CrossTable(Month, Data, 4)

LOAD AC1,

    AC2,

    AC3,

    AC4,

    Jan,

    Feb,

    Mar,

    Apr,

    May,

    Jun,

    Jul,

    Aug,

    Sep,

    Oct,

    Nov,

    Dec

FROM

Community_158716.xlsx

(ooxml, embedded labels, table is Sheet1);

[ramoncova06]

when you open the crosstable function just change you qualifier field to 4

it will give you this info

[sunny_talwar]

Did some further manipulations to get Month as Datefield instead of string:

Table:

CrossTable(Month, Data, 4)

LOAD AC1,

    AC2,

    AC3,

    AC4,

    Jan,

    Feb,

    Mar,

    Apr,

    May,

    Jun,

    Jul,

    Aug,

    Sep,

    Oct,

    Nov,

    Dec

FROM

Community_158716.xlsx

(ooxml, embedded labels, table is Sheet1);

Table1:

LOAD AC1,

  AC2,

  AC3,

  AC4,

  Num#(AC1&AC2&AC3&AC4, '####') as Acct,

  Month(Date#(Month, 'MMM')) as Month,

  Data

Resident Table;

DROP Table Table;

HTH

Best,

Sunny