Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

krishna4bo
Contributor

How to split data into multiple columns based on condition?

Hi All,

How to split data into multiple columns based on condition?

Raw data:

A

Apple : Banana

Orange : Banana

Apple - Banana -Milk

Apple - Banana -Milk - Coffee

Orange : Banana - Tea

Output:

A

B

C

Apple : Banana

Apple

Banana

Orange : Banana

Orange

Banana

Apple - Banana -Milk

Apple

Banana -Milk

Apple - Banana -Milk - Coffee

Apple

Banana -Milk -Coffee

Orange : Banana - Tea

Orange

Banana - Tea

Thanks,

Krishna.

4 Replies
Partner
Partner

Re: How to split data into multiple columns based on condition?

Try

subfield(A,':',1) as B

Subfield(A,':',2) as C

krishna4bo
Contributor

Re: How to split data into multiple columns based on condition?

Hi Shraddha,

Thanks for u r replay,but the out put is not  showing some records like with '-'

.   

ABC
Apple - Banana -MilkApple - Banana -Milk
Apple - Banana -Milk - CoffeeApple - Banana -Milk - Coffee
Apple : BananaApple Banana
Orange : BananaOrange Banana
Orange : Banana - TeaOrange Banana - Tea

Thanks,

Krishna

Partner
Partner

Re: How to split data into multiple columns based on condition?

Try

if(index(A,':')>0,subfield(A,':',1) ,subfield(A,'-',1) ) as B,

if(index(A,':')>0,subfield(A,':',2) ,subfield(A,'-',2) ) as C,

thiago_pinho
New Contributor III

Re: How to split data into multiple columns based on condition?

In your Example you're using different separators, is that so?