Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Region | Region with country |
APAC without Japan | APAC without Japan (row 2-12) |
APAC without Japan, EMEA | EMEA ( row 3-6, 8-11, 13-17, 19-21) |
APAC without Japan, EMEA, North America | North America (row 4,5,9,10,12,14,15,20,22,23) |
APAC without Japan, EMEA, North America, South America | South America (row 5,6,10,11,15-17,20,21,24) |
APAC without Japan, EMEA, South America, North America | Japan(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!
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.
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 *;
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.
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
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
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
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 *;
Thanks for the support!