comma separated values

[#1]

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

[sunny_talwar]

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

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

[#3]

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

}

[sunny_talwar]

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

[#5]

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.

[swuehl]

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.

[jonathandienst]

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

[#8]

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;

[jonathandienst]

Which is the 10 column data? How would I identify that?

[jonathandienst]

And if you put them in two tables with the same field names you will land up with a messy synthetic key. If they hae many fields in common, it is usually better to combine them into a single table.