Data Loading

Anonymous · ‎2017-12-06

Please help me to how load this file cross loading

		Direct	Direct	Direct	Direct	Direct	Direct	Direct	Direct	Direct	Direct
		CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj	CC Total w IR to ER Adj
		PROD	PROD	PROD	PROD	PROD	PROD	PROD	PROD	PROD	PROD
		No Trading Partner	Total Trading Partner	No Trading Partner	Total Trading Partner	No Trading Partner	Total Trading Partner	No Trading Partner	Total Trading Partner	No Trading Partner	Total Trading Partner
		Actual	Actual	Budget	Budget	O6	O6	O10	O10	Actual	Actual
		Final	Final	Final	Final	Final	Final	Final	Final	Final	Final
		2017	2017	2017	2017	2017	2017	2017	2017	2017	2017
		Oct	Oct	Nov	Nov	Nov	Nov	Nov	Nov	Nov	Nov
		USD_RPT	USD_RPT	USD_RPT	USD_RPT	USD_RPT	USD_RPT	USD_RPT	USD_RPT	USD_RPT	USD_RPT
Customer Name	Profit Center	Revenue	R61000	Revenue	R61000	Revenue	R61000	Revenue	R61000	Revenue	R61000
BANCO MONEX, S.A_	MS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
BANCO MONEX, S.A_	SI	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
BANCO MONEX, S.A_	BD	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
BANCO MONEX, S.A_	CS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Deutsche Bank_	MS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Deutsche Bank_	SI	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Deutsche Bank_	BD	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Deutsche Bank_	CS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
DTCC_	MS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
DTCC_	SI	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
DTCC_	BD	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
DTCC_	CS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Eaton Vance corp_	MS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Eaton Vance corp_	SI	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Eaton Vance corp_	BD	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Eaton Vance corp_	CS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
FEDERATED INVESTORS_	MS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
FEDERATED INVESTORS_	SI	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
FEDERATED INVESTORS_	BD	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
FEDERATED INVESTORS_	CS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Franklin Templeton_	MS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Franklin Templeton_	SI	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Franklin Templeton_	BD	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Franklin Templeton_	CS	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx

petter · ‎2017-12-06

By doing two loads you can keep the label lines in a separate but connected reference table. Then you can do a CrossLoad:

LABELS:
LOAD
'@' & (RowNo()+2) AS D#,*;
LOAD @1 AS D1, 
     @2 AS D2, 
     @3 AS D3, 
     @4 AS D4, 
     @5 AS D5, 
     @6 AS D6, 
     @7 AS D7, 
     @8 AS D8, 
     @9 AS D9, 
     @10 AS D10
FROM
[CrossTable.tsv]
(txt, codepage is 1252, no labels, delimiter is '\t', msq, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 11), Pos(Bottom, 1), Select(1, 0))),
Rotate(left)
));


DATA:
CrossTable(D#, Data, 2)
LOAD * FROM
[CrossTable.tsv]
(txt, codepage is 1252, no labels, delimiter is '\t', msq, header is 10 lines);

RENAME FIELDS @1 TO NAME, @2 TO CODE;

Related Topics