7 Replies Latest reply: Jan 31, 2017 3:21 AM by Владимир Фасюра RSS

    Dynamic split field into columns in script

    Audit Analytics

      Hi there,

      I am looking for help on the following issue.

      I have a table with many columns which contain multiple-value comma-separated values. The number of comma separated values varies.

       

      • Fields are as follows
        Project ID
        Project Name
        Project Resources (contain multiple entries)
        Countries covered (contain multiple entries)

      I've searched and found entries on subfields, cross table and peek etc, but as I am a newbie, cannot seem to make this work for my data.

      Please assist. The sample table is below.

       

      My initial thought is to do the below (but I have no idea how to do this in Qlikview!)

       

      1) Split 'Project Resources' and 'Countries covered' into columns (challenge - variable number of entries - in the full data you can have up to 50 countries covered).

       

      2) Some sort of cross-table, creating a row for each column (challenge - several columns with multiple comma separated values. Might create 'untrue' relationships between the individual values once split).

       

      3) My solution for 2 above is to use the 'Project Resources' and 'Countries covered' as variables.

       

      So, for the below list, for record PPM01 I would expect

         

      Project IDProject NameVariableValue
      PPM01Project 1Project ResourceTimothy
      PPM01Project 1Project ResourceJerry
      PPM01Project 1Project ResourceJames
      PPM01Project 1Countries coveredThailand
      PPM01Project 1Countries coveredMauritania
      PPM01Project 1Countries coveredIran
      PPM01Project 1Countries coveredCroatia

       

      And finally, I need Project Resource, Countries covered and Project Name to be separate list boxes/ selections on the sheet.

       

      I hope I can find help on here, much anticipating your responses!

       

       

         

      Project IDProject NameProject ResourcesCountries covered
      PPM01Project 1Timothy,Jerry,JamesThailand,Mauritania,Iran,Croatia
      PPM02Project 2Karl,Jerry,Timor-Leste
      PPM03Project 3Timothy,Jerry,PeterCosta Rica,Saudi Arabia,East Timor (see Timor-Leste),Georgia,Namibia
      PPM04Project 4BiJerry,Stephen,Comoros,Moldova,Grenada,Burundi
      PPM05Project 5Christian,Harry,Jamaica,Pakistan,Qatar,Panama
      PPM06Project 6Victor,Jerry,Cabo Verde,Oman,Afghanistan,Peru,Nigeria
      PPM07Project 7BiJerry,Stephen,PeterCambodia,Togo,Liberia,Mauritania,Dominica
      PPM08Project 8Karl,Stephen,CaroIsrael,Thailand,Chile,Chad
      PPM09Project 9Victor,Stephen,JamesDenmark,Australia,North Korea,Turkmenistan,Portugal
      PPM10Project 10Timothy,Stephen,Morocco,Macau,Suriname
      PPM11Project 11Christian,Jerry,PeterIndia,Germany
      PPM12Project 12Timothy,Harry,Burkina Faso,Korea, South,South Korea,Nauru
      PPM13Project 13Christian,Stephen,Tanzania
      PPM14Project 14Karl,Stephen,East Timor (see Timor-Leste),Saint Lucia,Israel,Uganda,Italy
      PPM15Project 15Victor,Harry,PeterBrunei,South Sudan,Latvia,Nigeria,Saint Lucia
      PPM16Project 16Karl,Jerry,JamesBenin,Zambia,Jamaica,Macedonia,New Zealand
      PPM17Project 17Precious,Harry,Tunisia
      PPM18Project 18Timothy,Jerry,Luxembourg,Latvia,Tanzania,Latvia,Korea, South
      PPM19Project 19BiJerry,Harry,Solomon Islands,Hong Kong,Romania,Mongolia
      PPM20Project 20Timothy,Stephen,Somalia,Canada,Nauru,China
      PPM21Project 21Victor,Harry,JamesNorth Korea,Bahamas, The,Namibia,Indonesia,Montenegro
      PPM22Project 22Precious,Harry,Algeria,Nigeria,Kenya,Marshall Islands,Norway
      PPM23Project 23Timothy,Jerry,JamesKuwait,Azerbaijan,Iceland,New Zealand,South Korea
      PPM24Project 24Timothy,Jerry,Pakistan,Thailand,Ghana,Sri Lanka,Iraq
      PPM25Project 25Christian,Harry,Mozambique,Djibouti,Slovenia,Korea, North,Tonga
      PPM26Project 26Victor,Jerry,Mauritius,Vietnam,Mozambique,Liberia,Romania
      PPM27Project 27Victor,Harry,Mali,Albania,Syria,Venezuela,Honduras
      PPM28Project 28Christian,Harry,Libya,Iceland,Hong Kong,Kazakhstan,Botswana
      PPM29Project 29Victor,Yemi,New Zealand,Colombia,Ethiopia,Zimbabwe
      PPM30Project 30Christian,Harry,Nepal,Bulgaria,Singapore,Aruba,Austria
      PPM31Project 31Timothy,Stephen,PeterKazakhstan,Palestinian Territories,Tuvalu,Spain,Serbia
      PPM32Project 32Karl,Jerry,Singapore,Ecuador,Lesotho,Macau
      PPM33Project 33BiJerry,Yemi,Burkina Faso,Philippines,Vanuatu,Tuvalu,Tanzania
      PPM34Project 34Karl,Harry,Morocco,Korea,United Arab Emirates,Brazil
      PPM35Project 35Christian,Stephen,PeterItaly,Kuwait,Solomon Islands,Singapore,Cyprus
      PPM36Project 36Timothy,Jerry,JamesTurkey,Central African Republic,Brunei,Mali
      PPM37Project 37Christian,Jerry,PeterUnited Kingdom,Algeria,Antigua and Barbuda,Belgium,Saint Kitts and Nevis
      PPM38Project 38Timothy,Stephen,Micronesia,Serbia,Bulgaria,
      PPM39Project 39Precious,Stephen,Bhutan,East Timor (see Timor-Leste),Barbados,Albania,Afghanistan
      PPM40Project 40Karl,Jerry,JamesDjibouti,Spain,Mexico,Liechtenstein
      PPM41Project 41Christian,Stephen,JamesCentral African Republic,Indonesia,Djibouti,Central African Republic,Belize
      PPM42Project 42Precious,Yemi,PeterPapua New Guinea,Uzbekistan,El Salvador,Ireland,Spain
      PPM43Project 43BiJerry,Stephen,Germany,Norway,Congo, Republic of the,Benin,Cambodia
        • Re: Dynamic split field into columns in script
          Marcus Sommer

          I think subfield() would be the best choice to create separate dimension-tables with something like this:

           

          IDtoCountry:

          load [Project ID], subfield(Country, ',') as Country from Source;

           

          Without the third parameter within subfield() in a load it will run like a loop and create for each item a record and also duplicate each other field-values in those records. Without any measure-field within your source-table you could do the subfield() there but I believe a split into several associated tables will be more suitable.

           

          - Marcus

          • Re: Dynamic split field into columns in script
            Muñoz Héctor

            Hi,

             

            Try this code:

             

            FACTS:

            LOAD [Project ID]        AS [FACT Project ID],

                 [Project Name]      AS [FACT Project Name],

                 [Project Resources] AS [FACT Project Resources],

                 [Countries covered] AS [FACT Countries covered]

            FROM .\FACTS.xlsx (ooxml, embedded labels, table is FACTS);

             

            PROJECTS:

            LOAD DISTINCT [FACT Project ID]   AS [Project ID],

                          [FACT Project Name] AS [Project Name]

            RESIDENT      FACTS;

             

            RESOURCES:

            LOAD DISTINCT [FACT Project ID]                       AS [Project ID],

                          SubField([FACT Project Resources], ',') AS Resource

            RESIDENT FACTS;

             

            COUNTRIES:

            LOAD DISTINCT [FACT Project ID]                       AS [Project ID],

                          SubField([FACT Countries covered], ',') AS Country

            RESIDENT FACTS;

             

            This is the result:

             

            27-01-2017 15-45-42.png

             

            And this is the model:

             

            27-01-2017 15-46-50.png

             

            I attach the QVW and excel files of the sample. I hope it serves to you...

             

            Regards,

            H

            • Re: Dynamic split field into columns in script
              Andrey Khoronenko

              Hi,

               

              Try code like this

               

              LOAD [Project ID],

                   [Project Name],

                   SubField([Project Resources], ',') as ProjectResources,

                   SubField([Countries covered], ',') as CountriesCovered    

              FROM

              (Your path to the data)

              While IterNo() < substringcount([Project Resources] , ',') + 1 Or IterNo() < Substringcount([Countries covered], ',') + 1;

               

              Regards,

              Andrey

              • Re: Dynamic split field into columns in script
                Wallo Atkinson

                Something like this perhaps.

                 

                load

                [Project ID],

                [Project Name],

                'Project Resources' as Variable,

                SubField([Project Resources], ',') as Value

                (YourTable)

                ;


                concatenate


                load

                [Project ID],

                [Project Name],

                'Countries Covered' as Variable,

                SubField([Countries Covered], ',') as Value

                (YourTable)

                ;