Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
holmlund
New Contributor III

Merge excel columns containing certain text?

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?

5 Replies
yousef_am
Contributor II

Re: Merge excel columns containing certain text?

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

s_kunte23
Contributor III

Re: Merge excel columns containing certain text?

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
New Contributor III

Re: Merge excel columns containing certain text?

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

s_kunte23
Contributor III

Re: Merge excel columns containing certain text?

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?

Hi,

one possible solution:

QlikCommunity_Thread_131737_Pic1.JPG.jpg

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

Community Browser