topic segregration of data in Connectivity & Data Prep

segregration of data

Anonymous — Mon, 12 Feb 2018 09:43:30 GMT

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

Re: segregration of data

juraj_misina — Mon, 12 Feb 2018 10:24:51 GMT

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

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 11:12:53 GMT

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

Re: segregration of data

juraj_misina — Mon, 12 Feb 2018 11:16:56 GMT

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;

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 11:30:32 GMT

Dear Juraj,

Should i Add the script in main ?

If so i am getting an error.

I have attached the Qlik app.

Regards,

Sachin

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 11:41:28 GMT

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

Re: segregration of data

juraj_misina — Mon, 12 Feb 2018 11:53:38 GMT

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

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 12:21:09 GMT

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,

Re: segregration of data

juraj_misina — Mon, 12 Feb 2018 12:34:07 GMT

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.

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 14:07:36 GMT

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

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 14:25:35 GMT

Hello,

I got the error and corrected it. Now I got more problems. Now Qlik doesnot filters in maps.

May be you can check the app.

Regards,

Sachin

Re: segregration of data

juraj_misina — Mon, 12 Feb 2018 15:12:35 GMT

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

Re: segregration of data

Anonymous — Mon, 12 Feb 2018 15:36:37 GMT

OMG This works perfectly

Thanks a lot Juraj

Re: segregration of data

Anonymous — Tue, 13 Feb 2018 16:25:35 GMT

Hello Juraj,

I got another problem relating to above script,

As the script divides the data in more rows.

The data from other columns are added.

For example: the count of this should be 5 but i get it as 7 because of the involved countries is saperated into new rows.

Can you please help me with this.

Thank you,

Sachin

Re: segregration of data

juraj_misina — Tue, 13 Feb 2018 20:53:16 GMT

Hello Sachin,

well, this is what I head in mind when I said you should be careful with SubField function. Solution depends on your data really, maybe you can keep Involved Countries field here unmodified and then load it again as a dimension. I can't really test it right now, but it might look like this.

[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] as CountryGroup,  
    [Branche],  
    [Project number],  
    [Comments],  
    [Turnover won ( € )],  
    [Turnover offered ( € )],  
    [F20],  
      
FROM [lib://Tender Handling/Report eFTM_CW6.xlsx]  
(ooxml, embedded labels, table is Base); 

Countries:  
LOAD
    [Involved Countries] as CountryGroup,  
    Trim(SubField([Involved Countries], ','))    as [Involved Countries],  
    APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER(Trim([Involved Countries])), '-')) AS [Base.Involved Countries_GeoInfo]  
FROM [lib://Tender Handling/Report eFTM_CW6.xlsx]  
(ooxml, embedded labels, table is Base);

This will create a table with countries and respective country grouping loaded from your excel file. It will be painted correctly on the map and also should provide correct results. But as I said, I can't test now, so you will probably need to play around a bit to fine tune this.

Hope this helps

Juraj

Re: segregration of data

Anonymous — Wed, 14 Feb 2018 11:59:15 GMT

Yep this worked but now the map doesnot show the countries that are listed together in same cell, like France is not highlighted. 😞

Re: segregration of data

juraj_misina — Wed, 14 Feb 2018 12:16:47 GMT

Hi,

yes, I forgot about the applymap:

Countries:
LOAD
     *,
     APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER(Trim([Involved Countries])), '-')) AS [Base.Involved Countries_GeoInfo]    
;
LOAD
    [Involved Countries] as CountryGroup,
    Trim(SubField([Involved Countries], ','))    as [Involved Countries]
FROM [lib://Tender Handling/Report eFTM_CW6.xlsx]
(ooxml, embedded labels, table is Base);

Juraj

Re: segregration of data

Anonymous — Wed, 14 Feb 2018 12:25:41 GMT

Dear Juraj,

I think you have solved all my doubts, for now.

Thanks a lot

Regards,

Sachin