Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Does all the column present in one table or different tables?
Pls provide sample data to understand better.
Regards,
Prabhu
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
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;
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
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;
Thanks Alexandar, You are awesome
You are welcome.