topic Re: How to get 2 colums from 1 column ? in QlikView

How to get 2 colums from 1 column ?

Mon, 28 Sep 2015 20:46:07 GMT

I have column called region

Region

USA

AUS

I want to get 2 fields called country and states

if region = USA,AUS then Country else States.

Country States

USA DC

USA VA

USA OH

AUS RT

AUS GY

AUS HA

HOW CAN Iget this done.Thank you.

Re: How to get 2 colums from 1 column ?

MarcoWedel — Mon, 28 Sep 2015 23:34:46 GMT

see your thread

Cross Table ,maybe

regards

Marco

Re: How to get 2 colums from 1 column ?

qlikviewwizard — Tue, 29 Sep 2015 01:24:48 GMT

Hi,

Use this script:

Data:

LOAD * INLINE [

Region

USA

AUS

];

NoConcatenate

Country:

LOAD Region as Country,Region

Resident Data

where WildMatch(Region,'USA','AUS');

NoConcatenate

State:

LOAD Region as State, 'USA' as Region

Resident Data

where WildMatch(Region,'DC','VA','OH');

join(State)

State1:

LOAD Region as State, 'AUS' as Region

Resident Data

where WildMatch(Region,'RT','GY','HA');

Drop table Data;

Re: How to get 2 colums from 1 column ?

Anonymous — Tue, 29 Sep 2015 03:27:03 GMT

Hi,

Use Cross table you can achieve your expected result.

Have a look:

Cross Table

Hope it helps!!

Re: How to get 2 colums from 1 column ?

senpradip007 — Tue, 29 Sep 2015 04:17:01 GMT

Have a look at this.

Tab1:

LOAD *,

if(match(Region, 'USA', 'AUS'), Region, peek(Country)) as Country,

Region as State

Inline [

Region

USA

AUS

];

T2:

NoConcatenate

LOAD

Country,

State

resident Tab1

where Country <> State;

DROP Table Tab1;

Re: How to get 2 colums from 1 column ?

Wed, 30 Sep 2015 13:44:45 GMT

Thank you veyr much