7 Replies Latest reply: May 27, 2014 7:00 AM by Alexander Petrov RSS

    Concatenate columns on the basis of 'IND%'

    Karunpreet Soni

      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%'

          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%'
              Karunpreet Soni

              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%'

                  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%'
                      Karunpreet Soni

                      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%'

                          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;