Gurus,
I got the below script from the community which combines two header rows into one in a crosstable. My challenge however is to rename some of the header columns in both Rows. Kindly assist
S1:
CrossTable(atr, Data, 2)
LOAD * FROM
crosstable.xlsx
(ooxml, no labels, table is Sheet1, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))
));
store S1 into S1.txt(txt);
drop Table S1;
S2:
CrossTable(atr, Data, 2)
LOAD * FROM
crosstable.xlsx
(ooxml, no labels, table is Sheet1, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0))),
Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))
));
store S2 into S2.txt(txt);
drop Table S2;
S3:
CrossTable(atr, Data, 2)
LOAD * FROM
crosstable.xlsx
(ooxml, no labels, table is Sheet1, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 2), Select(1, 0))),
Remove(Row, RowCnd(Interval, Pos(Bottom, 1), Pos(Bottom, 1), Select(1, 0)))
));
store S3 into S3.txt(txt);
drop Table S3;
S1:
LOAD
atr,
if(Data='',Previous(Data),Data) as head1
FROM
S1.txt
(txt, utf8, embedded labels, delimiter is ',', msq);
Left join(S1)
LOAD
atr,
Data as head2
FROM
S2.txt
(txt, utf8, embedded labels, delimiter is ',', msq);
Left join(S1)
LOAD @1 as npp,
@2 as naim,
atr,
Data
FROM
S3.txt
(txt, utf8, embedded labels, delimiter is ',', msq);
drop Field atr from S1;
Thanks
