Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

comma separated values

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


Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: comma separated values

Hi,

Sorry I should have use Len() like below

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



Hope this helps you.


Regards,

jagan.

14 Replies
MVP
MVP

Re: comma separated values

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

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

Not applicable

Re: comma separated values

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

}

MVP
MVP

Re: comma separated values

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

Not applicable

Re: comma separated values

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.

MVP
MVP

Re: comma separated values

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.

MVP
MVP

Re: comma separated values

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

Not applicable

Re: comma separated values

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;

MVP
MVP

Re: comma separated values

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

MVP
MVP

Re: comma separated values

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.