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

[#2]

No, i will not put them in same field names.

Actually i have 3 different types of data in one 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

you can see above the sample data of 3 different types. the first two types i have adding in one table with the above code i have added.

but in the same table the 3rd type data is also adding now, i need to separate the third type data and put in new table with different field names.

[#3]

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

[jagan]

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.

[#5]

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;

[jagan · Accepted solution]

Hi,

Sorry I should have use Len() like below

WHERE Len(KeepChar(budget_calc_profile_value, ',')) >= 15;

Hope this helps you.

Regards,

jagan.