topic Re: Merge excel columns containing certain text? in QlikView

Merge excel columns containing certain text?

holmlund — Wed, 03 Sep 2014 09:30:29 GMT

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.

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?

Re: Merge excel columns containing certain text?

Yousef_Amarneh — Wed, 03 Sep 2014 09:34:23 GMT

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

Re: Merge excel columns containing certain text?

Anonymous — Wed, 03 Sep 2014 15:34:35 GMT

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]);

- Stefan

Re: Merge excel columns containing certain text?

holmlund — Wed, 03 Sep 2014 19:48:11 GMT

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

Re: Merge excel columns containing certain text?

Anonymous — Wed, 03 Sep 2014 21:45:19 GMT

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.

Re: Merge excel columns containing certain text?

MarcoWedel — Wed, 03 Sep 2014 23:13:12 GMT

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