Merge excel columns containing certain text?

[holmlund]

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?

[Yousef_Amarneh]

can you share an excel file contains a sample of your data and the column that you want to generate .

[Anonymous]

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

[holmlund]

Unfortunately pre- and postfix changes constantly. Only middle part that is fixed...

[Anonymous]

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.  

[MarcoWedel]

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