topic Re: Concatenate columns on the basis of 'IND%' in QlikView

Concatenate columns on the basis of 'IND%'

Anonymous — Mon, 26 May 2014 09:52:48 GMT

Hello Everyone,

I have 6 columns which start with IND. I want to concatenate all of them.

I dont want to use IND1 & IND2 & IND3..till IND6, because after every week I will have another column with IND7 and then I will need to concatenate that too. So I want a general solution for it.

Thanking everyone in anticipation

Re: Concatenate columns on the basis of 'IND%'

Mon, 26 May 2014 10:11:12 GMT

Does all the column present in one table or different tables?

Pls provide sample data to understand better.

Regards,

Prabhu

Re: Concatenate columns on the basis of 'IND%'

Anonymous — Mon, 26 May 2014 10:20:52 GMT

All are present in the same table . For Eg:

Load * Inline [

A,B,C,IND1,IND2,IND3,IND4,IND5,IND6

1,2,3,4,5,6,7,8,9

1,1,1,1,1,1,1,1,1

];

I want to concatenate all the columns whose name starts with 'IND' into another column Indus.

Now, If I concatenate IND1,IND2...till IND6 then Indus will have two rows with values 456789 and 111111.

After a week another column IND7 will be added to the table and the value of the same should be concatenated i.e now IND1,IND2..till IND7 will be concatenated into Indus.

I need a general solution for this

Re: Concatenate columns on the basis of 'IND%'

Mon, 26 May 2014 11:00:45 GMT

Hi.

You may try this script:

test:

Load * Inline [

A,B,C,IND1,IND2,IND3,IND4,IND5,IND6

1,2,3,4,5,6,7,8,9

1,1,1,1,1,1,1,1,1

];

SET vFirstField = 4; //Set first concatenated field here

LET vFieldsNum = NoOfFields('test');

LET vFields=FieldName($(vFirstField),test);

FOR vFieldNo = $(vFirstField)+1 to $(vFieldsNum)

LET vFields= '$(vFields)'&if(IsNull(FieldName($(vFieldNo),test)),'','&'&FieldName($(vFieldNo),test));

NEXT;

result:

LOAD A,B,C, $(vFields) as Concat Resident test;

DROP Table test;

Re: Concatenate columns on the basis of 'IND%'

Anonymous — Mon, 26 May 2014 11:45:36 GMT

Hi Alexandar,

Thanks for your code. Its working for me , but again I have a doubt, if the columns are of type

IND_1_1, IND_2_1,IND_1_2, IND_2_2,IND_1_3,IND_2_3 and I want to concatenate columns starting with IND_1 and IND_2 separately into two columns IND1 and IND2 then how is it possible

Re: Concatenate columns on the basis of 'IND%'

Mon, 26 May 2014 14:12:16 GMT

Hi.

This is completed solution for you.

Supports any types delimiters.

Fields may have multiply number of prefixes, minor and major indexes.

Concatenated columns may have random order.

Names of concatenated columns must have this format: {Prefix}{Delimiter}{Major Index}{Delimiter}{Minor Index}

Script:

test:

Load * Inline [

A,B,C,IND_1_1,IND_1_2,IND_2_3,IND_2_4,IND_1_5,IND_2_6

1,2,3,4,5,6,7,8,9

1,1,1,1,1,1,1,1,1

];

SET vFirstField = 4;

LET vFieldsNum = NoOfFields('test');

SET vDelimiter = '_'; //Set delimiter here

fields_spec:

LOAD * Inline [

Name, Prefix, MajorIdx, MinorIdx

];

FOR vFieldNo = $(vFirstField) to $(vFieldsNum)

LET vName = FieldName($(vFieldNo),'test'); //Get field name

LET vPrefix = subfield(FieldName($(vFieldNo),'test'),'$(vDelimiter)',1); // Get field prefix

LET vMajorIdx = subfield(FieldName($(vFieldNo),'test'),'$(vDelimiter)',2); // Get field major index

LET vMinorIdx = subfield(FieldName($(vFieldNo),'test'),'$(vDelimiter)',3); // Get field minor index

Concatenate(fields_spec) // add field info to fields specification table

LOAD * Inline [

Name, Prefix, MajorIdx ,MinorIdx

$(vName), $(vPrefix), $(vMajorIdx), $(vMinorIdx)

];

NEXT;

Prefixes: //get all unique ORDERED values

LOAD Distinct Prefix Resident fields_spec Order By Prefix;

tempPrefixesCount:

LOAD Count(DISTINCT Prefix) as PrefixesCount Resident Prefixes;

LET vPrefixesNum = Peek('PrefixesCount',0,'tempPrefixesCount');

DROP Table tempPrefixesCount;

FOR vPrefixIdx=0 to $(vPrefixesNum)-1 //for each prefix

LET vPrefix = Peek('Prefix',$(vPrefixIdx),'Prefixes');

MajorIdxs:

LOAD Distinct MajorIdx Resident fields_spec Where Prefix='$(vPrefix)' Order By MajorIdx;

tempMajorIdxsCount:

LOAD Count(DISTINCT MajorIdx) as MajorIdxsCount Resident MajorIdxs;

LET vMajorIdxsNum = Peek('MajorIdxsCount',0,'tempMajorIdxsCount');

DROP Table tempMajorIdxsCount;

LET vLoadMajor = '';

FOR vMajorIdxIdx = 0 to $(vMajorIdxsNum)-1 // for each major index

LET vMajorIdx = Peek('MajorIdx',$(vMajorIdxIdx),'MajorIdxs');

MinorIdxs:

LOAD Distinct MinorIdx Resident fields_spec Where Prefix='$(vPrefix)' and MajorIdx='$(vMajorIdx)' Order By MinorIdx;

tempMinorIdxsCount:

LOAD Count(DISTINCT MinorIdx) as MinorIdxsCount Resident MinorIdxs;

LET vMajorIdxsNum = Peek('MinorIdxsCount',0,'tempMinorIdxsCount');

DROP Table tempMinorIdxsCount;

LET vLoad = '';

FOR vMinorIdxIdx = 0 to $(vMajorIdxsNum)-1

LET vMinorIdx = Peek('MinorIdx',$(vMinorIdxIdx),'MinorIdxs');

LET vLoad = '$(vLoad)'&if($(vMinorIdxIdx)=0,'','&')&'$(vPrefix)'&'$(vDelimiter)'&'$(vMajorIdx)'&'$(vDelimiter)'&'$(vMinorIdx)'; //constract concatenation query

NEXT vMinorIdxIdx;

DROP Table MinorIdxs;

LET vLoadMajor ='$(vLoadMajor)'&if($(vMajorIdxIdx)=0,'',',')&'$(vLoad)'&' as $(vPrefix)'&'$(vDelimiter)'&'$(vMajorIdx)';//add concatenation query to all prefixes

NEXT vMajorIdxIdx;

DROP Table MajorIdxs;

NEXT vPrefixIdx;

DROP Table Prefixes;

DROP Table fields_spec;

result:

LOAD A,B,C, $(vLoadMajor) Resident test; //paste our query into load statment

DROP Table test;

Re: Concatenate columns on the basis of 'IND%'

Anonymous — Tue, 27 May 2014 10:57:41 GMT

Thanks Alexandar, You are awesome

Re: Concatenate columns on the basis of 'IND%'

Tue, 27 May 2014 11:00:51 GMT

You are welcome.