CrossTable data with more than one header

[#1]

Hi

I have data in my below Exel file which I would like to cross table.

Q1, V1 resents data for January [Quantity] and [Value]

Q2, V2 represents data for February [Quantity] and [Value] ...etc

How can I structure this data to be in a logical table:

SBUDA, SBUDD, Month, Quantity, Value?

[john_s_w_wood · Accepted solution]

might have a solution for you Tumelo. Here's the script, attached sample qvw

tempData:

CrossTable(Field, Value, 2) // first 2 fields are in rows, label rest of data as field and value

LOAD *  // wildcard used to replace fields to save space but also accomodates new fields

FROM

TBUDEXPY.xlsx

(ooxml, embedded labels, table is TBUDEXPY);

NoConcatenate

tempData2: // only pull data where field starts with a Q, take right characters after Q as Month

load SBUDA,SBUDD, Right(Field,len(Field)-1) as Month, Value as Quantity resident tempData where left(Field,1) = 'Q';

Concatenate

//concatenate, only pull data where field starts with a V, take right characters after V as Month

load SBUDA,SBUDD,Right(Field,len(Field)-1) as Month, Value resident tempData where left(Field,1) = 'V';

Drop Table tempData;// no longer required

noconcatenate

Data: // consolidate into single row combinations

Load SBUDA,SBUDD,Month, sum(Quantity) as Quantity, sum(Value) as Value resident tempData2

group by SBUDA,SBUDD, Month;

Drop Table tempData2;// no longer required

[sunny_talwar]

You can also try this:

Table:

CrossTable(Quantity|Value, Data, 2)

LOAD SBUDA,

    SBUDD,

    Q1&'|'&V1,

    Q2&'|'&V2,

    Q3&'|'&V3,

    Q4&'|'&V4,

    Q5&'|'&V5,

    Q6&'|'&V6,

    Q7&'|'&V7,

    Q8&'|'&V8,

    Q9&'|'&V9,

    Q10&'|'&V10,

    Q11&'|'&V11,

    Q12&'|'&V12

FROM

Community_163620.xlsx

(ooxml, embedded labels, table is TBUDEXPY);

Table1:

LOAD SBUDA,

  SBUDD,

  PurgeChar(PurgeChar(Quantity|Value, '&|'), Chr(39)) as DataType,

  SubField(Data, '|', 1) as Quantity,

  SubField(Data, '|', 2) as Value

Resident Table;

DROP Table Table;

Best,

Sunny

[#4]

Wow! Thanks a lot guys, both your solutions are awesome.

John: Is there a way I can convert Month from 1-12 to Jan-Dec in your solution?

[john_s_w_wood]

way i do it is pick(monthNo,'Jan','Feb',etc)  so if monthno is say 6 it will return Jun. There might be a slicker way using an inline table with 2 columns monthno and month for a lookup or mapping load or playing with month(makedate(2000,monthno,1)).

[sunny_talwar]

You can do this to convert numbers to Month Names:

Dual(Pick(Month,  'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), Month) as Month

[#7]

Thanks a lot guys!