Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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;

View solution in original post

13 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist
Specialist

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist
Specialist

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

Highlighted
Not applicable

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

Highlighted
MVP & Luminary
MVP & Luminary

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;

View solution in original post

Highlighted
Not applicable

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)),

    .....

Highlighted
Not applicable

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?

Highlighted
MVP & Luminary
MVP & Luminary

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.