Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asinghal0412
Partner - Creator
Partner - Creator

Changing Data Model/Loading Script

Dear Qlik Community,

I am attaching below a Excel format. In the column A you can see the field 'Region' as specified in the 'Data Model'. As you can see it is not very efficient if I want to access a 'specific region'. I want to remodel this field as in Column B.

For example- in the new column if I select 'APAC without japan' it should select rows (2-12) of Column A, same for others.

RegionRegion with country
APAC without JapanAPAC without Japan (row 2-12)
APAC without Japan, EMEAEMEA ( row 3-6, 8-11, 13-17, 19-21)
APAC without Japan, EMEA, North AmericaNorth America (row 4,5,9,10,12,14,15,20,22,23)
APAC without Japan, EMEA, North America, South AmericaSouth America (row 5,6,10,11,15-17,20,21,24)
APAC without Japan, EMEA, South America, North AmericaJapan(row 7-11,18-22)
APAC without Japan, Japan 
APAC without Japan, Japan, EMEA 
APAC without Japan, Japan, EMEA, North America 
APAC without Japan, Japan, EMEA, North America, South America 
APAC without Japan, Japan, EMEA, South America 
APAC without Japan, North America 
EMEA 
EMEA, North America 
EMEA, North America, South America 
EMEA, South America 
EMEA, South America, North America 
Japan 
Japan, EMEA 
Japan, EMEA, North America, South America 
Japan, EMEA, South America 
Japan, North America 
North America 
South America 

 

Can some1 help me? 

Thanks, AS!

2 Solutions

Accepted Solutions
jbhappysocks
Creator II
Creator II

Hi

trim(subfield(Region,',')) as Region will split you Region and create your specific regions.

 

But be very careful when you create measures as this will create duplicate rows.

I don't know hoe your data model looks or what is connected to your areas, but lets say it's a Customer to give an example.

A Customer in the Japan, North America region with Sum(Sales) = 100 will now be two Customers, one in Japan and one in North America, but with same sales. -> Sum(Sales) = 200.

 

 

View solution in original post

jbhappysocks
Creator II
Creator II

There are many ways to achieve that, one way is by using the fact that your regions are comma delimited

This will give you a yes/no dimension with value regions, and also a dimension with the number of values for every region.

QUALIFY *;
Island:
Load Distinct
Region,
trim(subfield(Region,',')) as RegionSpecific,
SubStringCount(Region,',')+1 as NoOfRegions,     
if(Index(Region,','),'No','Yes') as SingleRegion               
Resident Data;
UNQUALIFY *;

UNQUALIFY *;

View solution in original post

6 Replies
jbhappysocks
Creator II
Creator II

Hi

trim(subfield(Region,',')) as Region will split you Region and create your specific regions.

 

But be very careful when you create measures as this will create duplicate rows.

I don't know hoe your data model looks or what is connected to your areas, but lets say it's a Customer to give an example.

A Customer in the Japan, North America region with Sum(Sales) = 100 will now be two Customers, one in Japan and one in North America, but with same sales. -> Sum(Sales) = 200.

 

 

asinghal0412
Partner - Creator
Partner - Creator
Author

hi,

Thanks for the response, but I do not want to create multiple records. Is there a way to achieve the separation but keep the records count the same?

 

Best,

Akash

jbhappysocks
Creator II
Creator II

Load something like this in your script 

QUALIFY *;
Island:
Load Distinct Region, trim(subfield(Region,',')) as RegionSpecific Resident Data;
UNQUALIFY *;

 

If Region is used as dimension 

Sum( {<Region = p(Island.Region) >}Value)

Use Island.Specic to filter data

 

 

 

 

 

 

asinghal0412
Partner - Creator
Partner - Creator
Author

Hi,

A quick question, I want to create another dimension, where I get only the regions which have only one value in the region column

for ex Row no 2, 13,18,23,24.

What should be the script expression?

Thanks,

AS

jbhappysocks
Creator II
Creator II

There are many ways to achieve that, one way is by using the fact that your regions are comma delimited

This will give you a yes/no dimension with value regions, and also a dimension with the number of values for every region.

QUALIFY *;
Island:
Load Distinct
Region,
trim(subfield(Region,',')) as RegionSpecific,
SubStringCount(Region,',')+1 as NoOfRegions,     
if(Index(Region,','),'No','Yes') as SingleRegion               
Resident Data;
UNQUALIFY *;

UNQUALIFY *;

asinghal0412
Partner - Creator
Partner - Creator
Author

Thanks for the support!