4 Replies Latest reply: Oct 29, 2013 10:48 AM by Onur Civelek RSS

    Load Redundand Fields

      Hi Togehter,

       

      following excellist list is given:

       

      Unbenannt.JPG.jpg

       

      Columns C to L are repeated in every each record.



      How can I get the repeating columns in the following Table transformation ?






      In this example, the first data set is transformed into 5 records.

      The second original data is transformed into 4 records because the redundant fields K and L contains nothing.

       

      The third data set produced only 3 records etc..


      How can I load on the first table from Excel, to derive the second Table from the first ?

       

       

      Thanks for your advice.

       

      Best Regards,

        • Re: Load Redundand Fields
          Celambarasan Adhimulam

          Could you post the script formed by the qlikview by choosing the file using Qlikview table files button?

          • Re: Load Redundand Fields
            Jaime Aguilar

            Hi,

            try this

             

            Redundant:

            LOAD site_id,

                site_name,

                category_id,

                categorie_name

            FROM

            RedundantFields.xlsx

            (ooxml, embedded labels, table is Sheet1);

             

            for i=1 to 4 //You need to load category_id and categorie_name 4 times besides the one that is already loaded

            Concatenate(Redundant)

            LOAD site_id,

                site_name,

                category_id$(i) as category_id,

                categorie_name$(i) as categorie_name

            FROM

            RedundantFields.xlsx

            (ooxml, embedded labels, table is Sheet1);

            NEXT

             

            Also take a look at the attached example,

             

            regards

              • Re: Load Redundand Fields
                Celambarasan Adhimulam

                Hi,

                Could you possibly try this

                Data:

                LOAD site_id,

                    site_name,

                    category_id & '|' & category_id1 & '|' &  category_id2 & '|' & category_id3 & '|' & category_id4 as category_id,

                    categorie_name & '|' & categorie_name1 & '|' &  categorie_name2 & '|' & categorie_name3 & '|' &      categorie_name4 as categorie_name

                FROM

                RedundantFields.xlsx

                (ooxml, embedded labels, table is Sheet1);

                 

                Result:

                LOAD

                    RowNo() AS UniqueNumber,

                    site_id,

                    site_name,

                    SubField(category_id, '|') AS category_id

                Resident Data;

                 

                Left Join(Result)

                LOAD

                    RowNo() AS UniqueNumber,

                    site_id,

                    site_name,

                    SubField(categorie_name, '|') AS categorie_name

                Resident Data;

                 

                DROP Table Data;

                • Re: Load Redundand Fields

                  Hi Jaime,

                   

                  thank you very much for your help. The Post from Celambarasan did solve my problem.

                   

                  Best Regards,