Concatenate columns on the basis of 'IND%'

[Anonymous]

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

[#2]

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

Pls provide sample data to understand better.

Regards,

Prabhu

[Anonymous]

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

[#4 · Accepted solution]

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;

[Anonymous]

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

[#6]

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;

[Anonymous]

Thanks Alexandar, You are awesome

[#8]

You are welcome.