Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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?
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)).
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
Thanks a lot guys!