segregration of data

[Anonymous]

Hello,

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

Description.

In Excel, I have the data like this

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

Is there any solution for this?

Thank you in advance,

Sachin

[juraj_misina]

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

[Anonymous]

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

[juraj_misina]

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;

[Anonymous]

Dear Juraj,

Should i Add the script in main ?

If so i am getting an error.

I have attached the Qlik app.

Regards,

Sachin

[Anonymous]

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] ;

[juraj_misina]

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

[Anonymous]

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)

how can i solve this issue?

regards,

[juraj_misina]

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.

[Anonymous]

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);