Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is there a way to merge Excel columns containing a certain column header text when loading Excel table into Qlikview?
I have a dynamicly changing Excel sheet with changing number of columns.
Columnnames are buildt up by prefix, stem an postfix.
Ex
1_Answer_1123342, 2_Answer_213231, ...
Is there an easy way to use wildcards or loops to concatenate all columns containing header text "Answer" into one Field?
can you share an excel file contains a sample of your data and the column that you want to generate .
Hi,
this script should work. But please note that this will only work when prefix and postfix do not change over several columns!
Columns:
load
@1 as Column1;
LOAD *
FROM
sample.xlsx
(ooxml, no labels, table is [My data], filters(
Transpose()
));
CleanCol:
load
'[' & concat(Column1, ']&[') & ']' as Old,
ColumnClean
group by subfield(del, '|', 2) ,ColumnClean;
load
subfield(subfield(Column1, ': ',2), '_',1) as ColumnClean,
Column1 & '|' &subfield(subfield(Column1, ': ',2), '_',1) as del,
Column1
Resident Columns where not isnull(subfield(subfield(Column1, ': ',2), '_',1)) ;
BaseTable:
load
RowNo() as NoRow,
Date,
DateTime,
Location
FROM
sample.xlsx
(ooxml, embedded labels, table is [My data]);
for i = 0 to (NoOfRows ('CleanCol') -1)
let vColOld$(i) = peek ('Old', $(i), 'CleanCol');
let vColNew$(i) = peek('ColumnClean', $(i), 'CleanCol');
left join (BaseTable)
load
RowNo() as NoRow,
$(vColOld$(i)) as [$(vColNew$(i))]
FROM
sample.xlsx
(ooxml, embedded labels, table is [My data]);
next
- Stefan
Unfortunately pre- and postfix changes constantly. Only middle part that is fixed...
yes, but are the characters which separate middle part from pre postfix always the same? Then you could handle it he way I posted in the script and just change the subfield parameters appropriately... don't know how your data exactly, depends on if your separating characters variate systematically or are random. Last case would be problematic. But could also be solved if you have a list of the fields without their post and prefixes (so "clear name") one could modify above script to check if these fields are contained in the table and then concatenate them to the complete field.
Hi,
one possible solution:
tabInput:
CrossTable (FieldName, FieldValue, 3)
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/600456-123094/sample.xlsx] (ooxml, embedded labels, table is [My data]);
tabOutput:
Generic LOAD
DateTime,
Location,
FieldNameComb,
Concat(FieldValue) as FieldValue
Group By DateTime, Location, FieldNameComb;
LOAD *, TextBetween(Replace(FieldName,'_',' '),' ',' ') as FieldNameComb
Resident tabInput;
DROP Table tabInput;
hope this helps
regards
Marco