topic Re: Separate data in 1 column in QlikView

Separate data in 1 column

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi peeps,

Newbie here, please be patient

Can you help me to separate this data into 2 column please...

So, i want to seperate into 2 column : WBS and Sales Order.

WBS is all starting with "B"

Sales Order is all starting with "3"

Cheers,

Aryo

Re: Separate data in 1 column

sunny_talwar — Wed, 07 Feb 2018 16:49:40 GMT

May be this

LOAD If(Left([WBS/Sales Order], 1) = 'B', [WBS/Sales Order]) as WBS,

If(Left([WBS/Sales Order], 1) = '3', [WBS/Sales Order]) as [Sales Order],

....

FROM ...

Re: Separate data in 1 column

shiveshsingh — Wed, 07 Feb 2018 16:49:48 GMT

if(left(DataColumn,1)=3,'Sales','WBS')

Re: Separate data in 1 column

jwjackso — Wed, 07 Feb 2018 16:58:42 GMT

You can use a preload statement:

Data:

LOAD

If(mid(WBSSalesOrder,1,1) = 'B',WBSSalesOrder,Null()) as WBS,

If(mid(WBSSalesOrder,1,1) = '3',WBSSalesOrder,Null()) as [Sales Order];

LOAD * Inline [

WBSSalesOrder

3005225829-000100

3005317005-000100

3005305555-000100

B0OC-0001092.02

3005332068-000100

B0OC-0021373.02

300536392-000100

3005364985-000100

3005444858-000100

];

Re: Separate data in 1 column

devarasu07 — Wed, 07 Feb 2018 17:00:50 GMT

Hi,

you can create like below

Fact:
LOAD [WBS/Sales Order] as WBS INLINE [
    WBS/Sales Order
    300522829-000100
    300317005-000100
    300505555-000100
    B0OC-0001092.02
    BOC-0021373.02
] where [WBS/Sales Order] like 'B*';
Concatenate
LOAD [WBS/Sales Order] as [Sales Order] INLINE [
    WBS/Sales Order
    300522829-000100
    300317005-000100
    300505555-000100
    B0OC-0001092.02
    BOC-0021373.02
] where [WBS/Sales Order] like '3*';