17 Replies Latest reply: Feb 14, 2018 7:25 AM by Sachin Nataraj RSS

    segregration of data

    Sachin Nataraj

      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

        • Re: segregration of data
          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

            • Re: segregration of data
              Sachin Nataraj

              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

                  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;
                  
                    • Re: segregration of data
                      Sachin Nataraj

                      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

                        • Re: segregration of data
                          Sachin Nataraj

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

                            [U],

                            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

                              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
                                  Sachin Nataraj

                                  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,

                                    • Re: segregration of data
                                      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.

                                        • Re: segregration of data
                                          Sachin Nataraj

                                          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
                                              Sachin Nataraj

                                              Hello,

                                               

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

                                              erd.PNG

                                              May be you can check the app.

                                               

                                              Regards,

                                               

                                              Sachin

                                                • Re: segregration of data
                                                  Juraj Misina

                                                  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],
                                                      [U]
                                                  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
                                                      Sachin Nataraj

                                                      OMG This works perfectly

                                                       

                                                      Thanks a lot Juraj

                                                        • Re: segregration of data
                                                          Sachin Nataraj

                                                          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.pap.PNG

                                                           

                                                          Can you please help me with this.

                                                           

                                                          Thank you,

                                                           

                                                          Sachin

                                                            • Re: segregration of data
                                                              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],  
                                                                  [U]  
                                                              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