How to get 2 colums from 1 column ?

[#1]

I have column called region

Region

USA

DC

VA

OH

AUS

RT

GY

HA

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.

[MarcoWedel]

see your thread

Cross Table ,maybe

regards

Marco

[qlikviewwizard]

Hi,

Use this script:

Data:

LOAD * INLINE [

Region

USA

DC

VA

OH

AUS

RT

GY

HA

];

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;

[Anonymous]

Hi,

Use Cross table you can achieve your expected result.

Have a look:

Cross Table

Hope it helps!!

[senpradip007 · Accepted solution]

Have a look at this.

Tab1:

LOAD *,

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

  Region as State

Inline [

Region

USA

DC

VA

OH

AUS

RT

GY

HA

];

T2:

NoConcatenate

LOAD

  Country,

  State

resident Tab1

where Country <> State;

DROP Table Tab1;

[#6]

Thank you veyr much