topic Re: Splitting the single column data in to multiple columns in QlikView

Splitting the single column data in to multiple columns

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

Hi Experts,

I am facing one issue in creating map in Qlik sense. I have Region, division and territory all in one column called Level name and its data in another column called Position Name . Hence i am writing if condition to get them as three different fields.

LOAD LEVEL_NAME,

PARENT_POSITION_NAME,

POSITION_NAME,

if(LEVEL_NAME='REGION',POSITION_NAME) as Region,

if(LEVEL_NAME='DIVISION',POSITION_NAME) as Division,

if(LEVEL_NAME='TERRITORY',POSITION_NAME) as Territory

FROM

(ooxml, embedded labels, table is Sheet1);

But i am not getting any association between these fields. Please find the snap shot below. I have attached qvf and data file as well.

my data is as below format ,using this data its very difficult to create association between Region,Division,Territory .

hence help me to split Level name field into three columns called Region,Division & Territory. And also help me to create the association between them.

stalwar1‌loveisfail‌

Re: Splitting the single column data in to multiple columns

amilafdo — Wed, 10 Jan 2018 04:26:26 GMT

Hi,

Use below code instead of the above code.

LOAD LEVEL_NAME,

if(LEVEL_NAME='DIVISION',LEVEL_NAME) as DIVISION,

if(LEVEL_NAME='REGION',LEVEL_NAME) as REGION,

if(LEVEL_NAME='NATION',LEVEL_NAME) as NATION,

if(LEVEL_NAME='TERRITORY',LEVEL_NAME) as TERRITORY,

PARENT_POSITION_NAME,

POSITION_NAME

Regards

Amila

Re: Splitting the single column data in to multiple columns

Anonymous — Wed, 10 Jan 2018 04:32:42 GMT

Use Hierarchy load instead of load

Hierarchy ‒ QlikView

Re: Splitting the single column data in to multiple columns

jonathandienst — Wed, 10 Jan 2018 04:40:45 GMT

You will need to do something like this:

TEMP:

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

FINAL:

LOAD LEVEL_NAME,

PARENT_POSITION_NAME,

POSITION_NAME,

POSITION_NAME as Region

RESIDENT TEMP

Where LEVEL_NAME='REGION';

Join(FINAL)

LOAD LEVEL_NAME,

PARENT_POSITION_NAME,

POSITION_NAME,

POSITION_NAME as Division

RESIDENT TEMP

Where LEVEL_NAME='DIVISION';

Join(FINAL)

LOAD LEVEL_NAME,

PARENT_POSITION_NAME,

POSITION_NAME,

POSITION_NAME as Territory

RESIDENT TEMP

Where LEVEL_NAME='TERRITORY';

DROP Table TEMP;

Re: Splitting the single column data in to multiple columns

neha_shirsath — Wed, 10 Jan 2018 06:47:01 GMT

Try below new field in your script-

if(WildMatch(LEVEL_NAME,'REGION'), 'Region',

if(WildMatch(LEVEL_NAME,'DIVISION'),'Division',

if(WildMatch(LEVEL_NAME,'TERRITORY'), 'Territory'))) as POSITION_NAME1