Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

sachinnataraj
New Contributor II

segregration of data

Hello,

Can you please help me in segregration of data which is in one column.

Description.

In Excel, I have the data like this

country.PNG

And in Qlik sene I cannot view in maps if there is more than one country in a row.

MAP.PNG

Is there any solution for this?

Thank you in advance,

Sachin

1 Solution

Accepted Solutions
Luminary
Luminary

Re: segregration of data

Hi,

I do not have the source file so I cannot reload the app to test this. Anyway, I guess the mechanism which tries to match country names with respective geo data got confused by our little trick here. Try replacing the whole part loading "base" table from the excel file with this:

[Base]:

LOAD

    *,

    APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER(Trim([Involved Countries])), '-')) AS [Base.Involved Countries_GeoInfo]

;

LOAD

    [Account],

    [Project name],

    [Status],

    [Status reason],

    [Full Name],

    [Account Manager/Owner],

    [Rhenus Products Tendered],

    [Type],

    [Turnover tendered ( € )],

    Date([Start date] ,'DD/MM/YYYY') AS [Start date],

    Date([End date] ,'DD/MM/YYYY') AS [End date],

    [Operational kick-off],

    [Project Source],

    Trim(SubField([Involved Countries], ','))    as [Involved Countries],

    [Branche],

    [Project number],

    [Comments],

    [Turnover won ( € )],

    [Turnover offered ( € )],

    [F20],

   

FROM [lib://Tender Handling/Report eFTM_CW6.xlsx]

(ooxml, embedded labels, table is Base);

In other words: Undo the subfield trick from the ApplyMap functions, because this seems to confuse the applymap and match single country with multiple coordinates, and do the ApplyMap on countries already processed (i.e. individual countries).

17 Replies
Luminary
Luminary

Re: segregration of data

Hi Sachin,

you can use SubField() function which will create a separate record for each part of a string divided by a delimiter. In your case the delimiter is a comma and a space, so combine it with Trim() to get rid of spaces. Try:

Trim(SubField([Involved Countries], ',')) as [Involved Countries]

Careful though, this really creates a separate record for each country so be extra careful when using this on fact table (I wouldn't recommend doing so unless you have a specific use case). As for dimension tables you should be generally OK, but double check your data.

Hope this helps

Juraj

sachinnataraj
New Contributor II

Re: segregration of data

Hello Juraj,

Thank you for your response.

As I am new to Qlik sense, I donot know how & where to use this function.

It would be great if you give me a detail step by step description. (sorry)

I am unable to add a .qvf file here (no option) !

Regards,

Sachin

Luminary
Luminary

Re: segregration of data

Hi

Use it in the load script when loading data from that particular source.

LOAD

     Trim(SubField([Involved Countries], ','))     as Country,

     Branches

//other fields from source

FROM Source;

sachinnataraj
New Contributor II

Re: segregration of data

Dear Juraj,

Should i Add the script in main ?

If so i am getting an error.

I have attached the Qlik app. error.PNG

Regards,

Sachin

sachinnataraj
New Contributor II

Re: segregration of data

May be this works?

I have the whole script.

Set dataManagerTables = '','Base';

//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)

    Let index = 0;

    Let currentName = name;

    Let tableNumber = TableNumber(name);

    Let matches = 0;

    Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

        index = index + 1;

        currentName = name & '-' & index;

        tableNumber = TableNumber(currentName)

        matches = Match('$(currentName)', $(dataManagerTables));

    Loop

    If index > 0 then

            Rename Table [$(name)] to [$(currentName)];

    EndIf;

Next;

__countryAliasesBase:

LOAD

  Alias AS [__Country],

  ISO3Code AS [__ISO3Code]

FROM [lib://__GEO_TABLES/countryAliases.qvd]

(qvd);

__countryGeoBase:

LOAD

  ISO3Code AS [__ISO3Code],

  ISO2Code AS [__ISO2Code],

  Polygon AS [__Polygon]

FROM [lib://__GEO_TABLES/countryGeo.qvd]

(qvd);

__countryName2IsoThree:

MAPPING LOAD

  __Country,

  __ISO3Code

RESIDENT __countryAliasesBase;

__countryCodeIsoThree2Polygon:

MAPPING LOAD

  __ISO3Code,

  __Polygon

RESIDENT __countryGeoBase;

[Base]:

LOAD

  [Account],

  [Project name],

  [Status],

  [Status reason],

  [Full Name],

  [Account Manager/Owner],

  [Rhenus Products Tendered],

  [Type],

  [Turnover tendered ( € )],

  Date([Start date] ,'DD/MM/YYYY') AS [Start date],

  Date([End date] ,'DD/MM/YYYY') AS [End date],

  [Operational kick-off],

  [Project Source],

  [Involved Countries],

  [Branche],

  [Project number],

  [Comments],

  [Turnover won ( € )],

  [Turnover offered ( € )],

  [F20],

  ,

  APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Involved Countries])), '-') AS [Base.Involved Countries_GeoInfo]

FROM [lib://Tender Handling/Report eFTM_CW6.xlsx]

(ooxml, embedded labels, table is Base);

TAG FIELD [Involved Countries] WITH '$geoname', '$relates_Base.Involved Countries_GeoInfo' ;

TAG FIELD [Base.Involved Countries_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_Involved Countries' ;

DROP TABLES __countryAliasesBase, __countryGeoBase;

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

  Month($1) AS [Month] Tagged ('$month', '$cyclic'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

  Year(Today())-Year($1) AS [YearsAgo] ,

  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

  Month(Today())-Month($1) AS [MonthRelNo] ,

  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Start date], [End date] USING [autoCalendar] ;

Luminary
Luminary

Re: segregration of data

Hi,

looking into your app you can do this in data manager using the "Split" functionality in Data Manager. Pictured an example from a different app:

Optionally you could unlock the autogenerated section and change line 61 (the "Involved countries" field) to

Trim(SubField([Involved Countries], ','))    as [Involved Countries].

Best

Juraj

sachinnataraj
New Contributor II

Re: segregration of data

Hello,


Trim(SubField([Involved Countries], ','))    as [Involved Countries].


This worked thanks a lot,


However, i got a new problem now.

The map shows only the countries that are individual in excel column (involved countries)


for example France is not highlighted as it was in list of countries that were in same cell. (Pictured below)vds.PNG

how can i solve this issue?


regards,

Luminary
Luminary

Re: segregration of data

Hi Sachin,

sorry, I should have caught this on the first try, but I don't work with data manager / auto-generated script that much. Looking at the script you should also change this line:

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Involved Countries])), '-') AS [Base.Involved Countries_GeoInfo]

to this:

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER(Trim(SubField([Involved Countries], ','))), '-') AS [Base.Involved Countries_GeoInfo]

This should do the trick.

sachinnataraj
New Contributor II

Re: segregration of data

Hello,

This doesnot work as it prompts an error (marked in bold)

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER(Trim(SubField([Involved Countries], ','))), '-') AS [Base.Involved Countries_GeoInfo]

FROM [lib://Tender Handling/Report eFTM_CW6.xlsx]

(ooxml, embedded labels, table is Base);

Community Browser