14 Replies Latest reply: Feb 1, 2016 11:18 PM by jagan mohan rao appala RSS

    comma separated values

    Kumar Pramod

      Hi all,

       

      jagan

       

      I have comma separated values in a column like below,

      1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

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


      from this i need to create two tables one which contain 10 columns and one for 16 columns.

      I need to put all the values in particular column.

      How can i do this? how can i check that comma separated values as 10 and 16?


      Regards,

      Pramod


        • Re: comma separated values
          Sunny Talwar

          To check you can simply do a count of commas + 1. Something like this:

           

          Len(KeepChar(FieldName, ',')) + 1

            • Re: comma separated values
              Kumar Pramod

              thanks for the reply sunny,

              but i have 16 and 10 expression which create a tables, like below

               

              If(parser_version='V2', SubField(budget_calc_profile_value, ',' ,1),SubField(budget_calc_profile_value, ',' ,16)) AS Data


              How can i write my expression where


              if(Len(KeepChar(FieldName, ',')) + 1> 13){

              16 expressions;

              } else {

              10 expressions

              }

                • Re: comma separated values
                  Sunny Talwar

                  Can you share how the data looks for just one row of budget_calc_profile_value

                    • Re: comma separated values
                      Kumar Pramod

                      Actually i have 3 different types of data for that column,

                       

                      1)  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

                      2)  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15|||16

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

                       

                      I have a 16 line expression like If(parser_version='V2', SubField(budget_calc_profile_value, ',' ,1),SubField(budget_calc_profile_value, ',' ,16)) AS Data

                      From this i am able to put data for 16 columns. but now for data having 10 commas have different columns name so i need different table to add the data.

                        • Re: comma separated values
                          Stefan Wühl

                          Maybe like

                           

                          LINE:
                          LOAD Line INLINE [
                          Line
                          "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16"
                          "1,2,3,4,5,6,7,8,9,10"
                          ];
                          
                          
                          QUALIFY *;
                          
                          
                          T1:
                          LOAD * From_Field (LINE, Line) (no labels, delimiter is ',')
                          ;
                          

                           

                           

                          You can map the field names to different names using a translation table if you want.

                          • Re: comma separated values
                            Jonathan Dienst

                            If you want two tables for the different length fields, then something like this script:

                             

                            TSource:

                            LOAD If(SubStringCount(Source, ',') = 15,

                              'B,' & Source,

                              'A,' & Source

                            ) As Source;

                            LOAD * Inline

                            [

                              Source

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

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

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

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

                            ] (delimiter is \t);

                             

                            TableA:

                            LOAD

                              @2 As A1,

                              @3 As A2,

                              @4 As A3,

                              @5 As A4,

                              @6 As A5,

                              @7 As A6,

                              @8 As A7,

                              @9 As A8,

                              @10 As A9,

                              @11 As A10

                            FROM_Field (TSource, Source)(txt, no labels)

                            WHERE @1 = 'A';

                             

                            TableB:

                            LOAD

                              @2 As B1,

                              @3 As B2,

                              @4 As B3,

                              @5 As B4,

                              @6 As B5,

                              @7 As B6,

                              @8 As B7,

                              @9 As B8,

                              @10 As B9,

                              @11 As B10

                              @12 As B11

                              @13 As B12

                              @14 As B13

                              @15 As B14

                              @16 As B15

                              @17 As B16

                            FROM_Field (TSource, Source)(txt, no labels)

                            WHERE @1 = 'B';

                             

                            t1.png

                              • Re: comma separated values
                                Kumar Pramod

                                Thanks jonathan, This is the what exactly i want but once you see my script:

                                 

                                From below script i am getting the 16 columns table which contains 10 columns data also.

                                 

                                Now i need to separate the 10 columns data. how can i do?

                                 

                                BUDGET_CALC_DATA_V2:

                                LOAD `user_id`,

                                    name as budget_calc_profile_name,

                                    value as budget_calc_profile_value,

                                    parser_version;

                                SQL SELECT `user_id`,

                                    name,

                                    value,

                                    parser_version

                                FROM rcdbprod.ACCOUNTPROFILE WHERE name="BUDGET_CALC_DATA" ;

                                 

                                 

                                BUDGET_CALC_V2:

                                LOAD `user_id`,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,1), SubField(budget_calc_profile_value, ',' ,-1)) as Income,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,2), SubField(budget_calc_profile_value, ',' ,1)) as creditcardpayments,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,3), SubField(budget_calc_profile_value, ',' ,2)) as Loanpayments,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,4), SubField(budget_calc_profile_value, ',' ,3)) as Groceries,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,5), SubField(budget_calc_profile_value, ',' ,4)) as Toiletries,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,6), SubField(budget_calc_profile_value, ',' ,5)) as EatingOut,  

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,7), SubField(budget_calc_profile_value, ',' ,6)) as Rent_or_mortgage_payment,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,8), SubField(budget_calc_profile_value, ',' ,7)) as Electricity_gas,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,9), SubField(budget_calc_profile_value, ',' ,8)) as Cable_Internet_provider,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,10), SubField(budget_calc_profile_value, ',' ,9)) as Cell_phone,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,11), SubField(budget_calc_profile_value, ',' ,10)) as Medical_expenses,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,12), SubField(budget_calc_profile_value, ',' ,11)) as Entertainment,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,13), SubField(budget_calc_profile_value, ',' ,12)) as Transportation,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,14), SubField(budget_calc_profile_value, ',' ,13)) as Insurance,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,15), SubField(budget_calc_profile_value, ',' ,14)) as Other,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,16), SubField(budget_calc_profile_value, ',' ,15)) as Savings;

                                    LOAD

                                    user_id,

                                    parser_version,

                                  Replace(Replace(Replace(budget_calc_profile_value, '|||', ','), '||', ','), '|', ',') AS budget_calc_profile_value

                                    Resident BUDGET_CALC_DATA_V2;

                                 

                                 

                                Drop Table BUDGET_CALC_DATA_V2;

                      • Re: comma separated values
                        Kumar Pramod

                        Hi jagan,

                         

                        you helped me one on my another post please check this once.

                         

                        Load comma separated values into different columns

                         

                        please help on this discussion.

                         

                        Regards,

                        Pramod

                          • Re: comma separated values
                            jagan mohan rao appala

                            HI,

                             

                            Try like this

                             

                            BUDGET_CALC_V2:

                            LOAD `user_id`,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,1), SubField(budget_calc_profile_value, ',' ,-1)) as Income,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,2), SubField(budget_calc_profile_value, ',' ,1)) as creditcardpayments,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,3), SubField(budget_calc_profile_value, ',' ,2)) as Loanpayments,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,4), SubField(budget_calc_profile_value, ',' ,3)) as Groceries,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,5), SubField(budget_calc_profile_value, ',' ,4)) as Toiletries,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,6), SubField(budget_calc_profile_value, ',' ,5)) as EatingOut, 

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,7), SubField(budget_calc_profile_value, ',' ,6)) as Rent_or_mortgage_payment,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,8), SubField(budget_calc_profile_value, ',' ,7)) as Electricity_gas,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,9), SubField(budget_calc_profile_value, ',' ,8)) as Cable_Internet_provider,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,10), SubField(budget_calc_profile_value, ',' ,9)) as Cell_phone,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,11), SubField(budget_calc_profile_value, ',' ,10)) as Medical_expenses,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,12), SubField(budget_calc_profile_value, ',' ,11)) as Entertainment,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,13), SubField(budget_calc_profile_value, ',' ,12)) as Transportation,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,14), SubField(budget_calc_profile_value, ',' ,13)) as Insurance,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,15), SubField(budget_calc_profile_value, ',' ,14)) as Other,

                            If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,16), SubField(budget_calc_profile_value, ',' ,15)) as Savings;

                                LOAD

                                user_id,

                                parser_version,

                              Replace(Replace(Replace(budget_calc_profile_value, '|||', ','), '||', ','), '|', ',') AS budget_calc_profile_value

                                Resident BUDGET_CALC_DATA_V2

                            WHERE KeepChar(budget_calc_profile_value, ',') = 16; // For loading 16 field values

                             

                             

                             

                             

                            condition for 10 field values

                             

                            WHERE KeepChar(budget_calc_profile_value, ',') = 10; // For loading 10 field values


                            Hope this helps you.


                            Regards,

                            Jagan.

                              • Re: comma separated values
                                Kumar Pramod

                                hi Jagan i used below scripts are loading successfully but for BUDGET_CALC:(16 fields data is not loading only empty fields) I used both "=16" and ">13" but not working.

                                 

                                If i remove the BUDGET_CALC_MOBILE table it will load properly.

                                 

                                Please help on this

                                 

                                BUDGET_CALC_MOBILE:

                                LOAD `user_id`,

                                SubField(budget_calc_profile_value, ',' ,1) as Income_Mob,

                                SubField(budget_calc_profile_value, ',' ,2) as Housing,

                                SubField(budget_calc_profile_value, ',' ,3) as Food,

                                SubField(budget_calc_profile_value, ',' ,4) as Transportation_Mob,

                                SubField(budget_calc_profile_value, ',' ,5) as CellPhone_Mob,

                                SubField(budget_calc_profile_value, ',' ,6) as LoanAndCards,

                                SubField(budget_calc_profile_value, ',' ,7) as MedicalAndDentalExpenses,

                                SubField(budget_calc_profile_value, ',' ,8) as Personal,

                                SubField(budget_calc_profile_value, ',' ,9) as Insurance_Mob,

                                SubField(budget_calc_profile_value, ',' ,10) as Savings_Mob

                                Resident BUDGET_CALC_DATA_V2 WHERE KeepChar(budget_calc_profile_value, ',') < 13;

                                 

                                 

                                BUDGET_CALC:

                                LOAD `user_id`,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,1), SubField(budget_calc_profile_value, ',' ,-1)) as Income,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,2), SubField(budget_calc_profile_value, ',' ,1)) as creditcardpayments,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,3), SubField(budget_calc_profile_value, ',' ,2)) as Loanpayments,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,4), SubField(budget_calc_profile_value, ',' ,3)) as Groceries,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,5), SubField(budget_calc_profile_value, ',' ,4)) as Toiletries,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,6), SubField(budget_calc_profile_value, ',' ,5)) as EatingOut,  

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,7), SubField(budget_calc_profile_value, ',' ,6)) as Rent_or_mortgage_payment,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,8), SubField(budget_calc_profile_value, ',' ,7)) as Electricity_gas,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,9), SubField(budget_calc_profile_value, ',' ,8)) as Cable_Internet_provider,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,10), SubField(budget_calc_profile_value, ',' ,9)) as Cell_phone,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,11), SubField(budget_calc_profile_value, ',' ,10)) as Medical_expenses,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,12), SubField(budget_calc_profile_value, ',' ,11)) as Entertainment,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,13), SubField(budget_calc_profile_value, ',' ,12)) as Transportation,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,14), SubField(budget_calc_profile_value, ',' ,13)) as Insurance,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,15), SubField(budget_calc_profile_value, ',' ,14)) as Other,

                                If(parser_version='V2',SubField(budget_calc_profile_value, ',' ,16), SubField(budget_calc_profile_value, ',' ,15)) as Savings;

                                LOAD

                                    user_id,

                                    parser_version,

                                  Replace(Replace(Replace(budget_calc_profile_value, '|||', ','), '||', ','), '|', ',') AS budget_calc_profile_value

                                    Resident BUDGET_CALC_DATA_V2 WHERE KeepChar(budget_calc_profile_value, ',') = 16;

                                 

                                 

                                Drop Table BUDGET_CALC_DATA_V2;