Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Load comma separated values into different columns

hi all,

I have a column with comma separated values like 1,2,3,4,5,6,7,8,9,10.

Can i separate this values into individuals columns with particular column name.

If possible Can anyone explain how can i do while loading the data.

Regards,

kumar

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Load comma separated values into different columns

Hi,

Try like this

BUDGET_CALC:

LOAD `user_id`,

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

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

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

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

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

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

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

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

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

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

    SubField(budget_calc_profile_value, ',' ,11) as expenses,

    SubField(budget_calc_profile_value, ',' ,12) as Entertainment,

    SubField(budget_calc_profile_value, ',' ,13) as Transportation,

    SubField(budget_calc_profile_value, ',' ,14) as Insurance,

    SubField(budget_calc_profile_value, ',' ,15) as Other,

    SubField(budget_calc_profile_value, ',' ,16) as Savings;

LOAD

user_id,

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

    Resident BUDGET_CALC_DATA;

13 Replies
MVP
MVP

Re: Load comma separated values into different columns

Hi,

Try generic load like below

Test:

LOAD ID, RowNo() AS No, SubField(Value, ',') AS SplitValue

INLINE [

    ID, Value

    1, "1,2,3,4,5"

];

LET vStart = NoOfTables();

Split:

Generic

LOAD

          *

Resident Test;

DROP TABLE Test;

LET vLoop = NoOfTables() - $(vStart);

//Rename first table name

RENAME Table Split.1 to Split;

FOR i = 1 to vLoop

LET vCurrentTable = 'Split.'& ($(i) + 1);

Outer Join(Split)

LOAD

          *

Resident

$(vCurrentTable);

DROP Table $(vCurrentTable);

NEXT

paul_scotchford
Valued Contributor

Re: Load comma separated values into different columns

One simple way is use the import wizard and when it evaluates the file it will show the column names, you can rename them from there. Once the load has completed the script will be there for you to use/change as you like moving forward.

MVP
MVP

Re: Load comma separated values into different columns

If it is a fixed number of values, and you know what each one is, you can use SubField

LOAD

  FieldName,

  SubField(FieldName, ', ') as SubField1,

  SubField(FieldName, ', ') as SubField2,

  ...

If your string has a variable number of values, then extra columns will be null.

It will be possible, if required, to CROSSTABLE the resultant file, to turn it into multiple rows, with a single column and multiple rows.

Hope that helps.

Steve

paul_scotchford
Valued Contributor

Re: Load comma separated values into different columns

My apologies, I misread your question.

The suggestions by Steve and Jagan are worth trying.

However, how is this source data originally created ? Is it going to be a regular data feed ?

Can the data provider modify their extraction process ?

Can you pre-process the file using SPSS , which will give you more power for transformation.

cheers

Paul

Not applicable

Re: Load comma separated values into different columns

Thanks for the reply jagan,

I have used below script,

LOAD `user_id`,

    name,

    value,

    `parser_version`;

SQL SELECT `user_id`,

    name,

    value,

    `parser_version`

FROM rcdb.accountprofile;

BUDGET_CALC_DATA:

LOAD `user_id`,

    name as budget_calc_profile_name,

    value as budget_calc_profile_value;

SQL SELECT `user_id`,

    name,

    value

FROM accountprofile WHERE name="BUDGET_CALC_DATA" ;

BUDGET_CALC:

LOAD `user_id`,

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

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

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

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

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

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

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

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

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

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

    SubField(budget_calc_profile_value, ',' ,11) as expenses,

    SubField(budget_calc_profile_value, ',' ,12) as Entertainment,

    SubField(budget_calc_profile_value, ',' ,13) as Transportation,

    SubField(budget_calc_profile_value, ',' ,14) as Insurance,

    SubField(budget_calc_profile_value, ',' ,15) as Other,

    SubField(budget_calc_profile_value, ',' ,16) as Savings

    Resident BUDGET_CALC_DATA;

   

Drop table BUDGET_CALC_DATA;

The issue is data has different delimiter , can i use the condition to check the delimiter?

Sample Data:

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

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15|||16  ----- Parser_version = V1

I have above types of data how can i differentiate these types and load data in single table, the delimiter "|||" is not working

MVP
MVP

Re: Load comma separated values into different columns

Hi,

Try like this

BUDGET_CALC:

LOAD `user_id`,

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

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

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

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

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

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

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

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

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

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

    SubField(budget_calc_profile_value, ',' ,11) as expenses,

    SubField(budget_calc_profile_value, ',' ,12) as Entertainment,

    SubField(budget_calc_profile_value, ',' ,13) as Transportation,

    SubField(budget_calc_profile_value, ',' ,14) as Insurance,

    SubField(budget_calc_profile_value, ',' ,15) as Other,

    SubField(budget_calc_profile_value, ',' ,16) as Savings;

LOAD

user_id,

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

    Resident BUDGET_CALC_DATA;

Not applicable

Re: Load comma separated values into different columns

How Can I use If condition?

Is below expression is correct?

if (parser_version="V2",(SubField(budget_calc_profile_value, ',' ,1) as Data),

    (SubField(budget_calc_profile_value, ',' ,-1) as Data)),

    .....

Not applicable

Re: Load comma separated values into different columns

In V2 the column1 value is in first place but in V1 it is 16 th place.

How can i differentiate and place 16 th place value to 1st column?

MVP
MVP

Re: Load comma separated values into different columns

HI,

Try like this

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

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

Hope this helps you.

Regards,

Jagan.