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

[Anonymous]

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

[juraj_misina · Accepted solution]

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

[Anonymous]

OMG This works perfectly

Thanks a lot Juraj

[Anonymous]

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

[juraj_misina]

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

[Anonymous]

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

[juraj_misina]

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

[Anonymous]

Dear Juraj,

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

Thanks a lot

Regards,

Sachin