8 Replies Latest reply: May 20, 2016 7:55 PM by Sunny Talwar RSS

    load excel

    Alex Pan

      Hi All

       

      I have a file that the headers are not in the same row. attached file

      Can Qlik do something so that the cells in the header can merge?

      look like this?

      Entity ID, 2012Q3, 2012Q4.....

       

      Thank you!

        • Re: load excel
          Sunny Talwar

          May be like this:

           

          Table:

          Directory;

          LOAD F1 as Entity,

               [2012Q3],

               [2012Q4],

               [2013Q1],

               [2013Q2],

               [2013Q3],

               [2013Q4],

               [2014Q1],

               [2014Q2],

               [2014Q3],

               [2014Q4],

               [2015Q1],

               [2015Q2],

               [2015Q3],

               [2015Q4],

               [2016Q1],

               [2016Q2]

          FROM

          [test (6).xlsx]

          (ooxml, embedded labels, header is 7 lines, table is [Entity Rating Trend Report]);

          • Re: load excel
            Santiago Respane

            Hi Alex,

            your excel it's not in the best condition to be loaded but you can do something like this:

             

            LOAD F1,

                 [2012Q3],

                 [2012Q4],

                 [2013Q1],

                 [2013Q2],

                 [2013Q3],

                 [2013Q4],

                 [2014Q1],

                 [2014Q2],

                 [2014Q3],

                 [2014Q4],

                 [2015Q1],

                 [2015Q2],

                 [2015Q3],

                 [2015Q4],

                 [2016Q1],

                 [2016Q2]

            FROM [test.xlsx]

            (ooxml, embedded labels, header is 7 lines, table is [Entity Rating Trend Report]);

             

            first field appears named as F1 because its header is not in the same row of the other felds.

             

            Let me know if this helps.

            Kind regards,

            • Re: load excel
              Settu Periyasamy

              One more option using Transformation Wizard.. (you can remove or fill whatever you have in that field).. for your file

              try like

               

              Table:

              LOAD * FROM

              [test.xlsx]

              (ooxml, embedded labels, header is 6 lines, table is [Entity Rating Trend Report], filters(

              Transpose(),Replace(1, right, StrCnd(null)),Remove(Col, Pos(Top, 2)),Transpose())) ;

              • Re: load excel
                Alex Pan

                Thanks everyone for responding.

                What if the report does not always come in where the heading occupy 6 rows (maybe more or less depends on the  month?) meaning Entity Id may land on any row. Is there a dynamic way to load this?

                  • Re: load excel
                    Sunny Talwar

                    So you are saying that next time the file comes, the Entity Id and the YearQuarter column can be in another row?

                      • Re: load excel
                        Alex Pan

                        You are correct.

                        I can't confirm that until next week but I am afraid that may happen.

                          • Re: load excel
                            Sunny Talwar

                            Here is a sample for you to play around with. Here test (7) has Entity Id starting at number 20, while test (6) has Entity Id at number 7.

                             

                            Script 1:

                             

                            //LET vFileName = 'test (7).xlsx';

                            LET vFileName = 'test (6).xlsx';

                             

                            Table:

                            LOAD Row

                            Where A = 'Entity Id';

                            LOAD RecNo() as Row,

                              A

                            FROM

                            [$(vFileName)]

                            (ooxml, no labels, table is [Entity Rating Trend Report]);

                             

                            LET vRow = Peek('Row');

                             

                            LOAD F1 as [Entity Id],

                                [2012Q3],

                                [2012Q4],

                                [2013Q1],

                                [2013Q2],

                                [2013Q3],

                                [2013Q4],

                                [2014Q1],

                                [2014Q2],

                                [2014Q3],

                                [2014Q4],

                                [2015Q1],

                                [2015Q2],

                                [2015Q3],

                                [2015Q4],

                                [2016Q1],

                                [2016Q2]

                            FROM

                            [$(vFileName)]

                            (ooxml, embedded labels, header is $(vRow) lines, table is [Entity Rating Trend Report]);

                             

                            Script 2:

                             

                            LET vFileName = 'test (7).xlsx';

                            //LET vFileName = 'test (6).xlsx';

                             

                            Table:

                            LOAD Row

                            Where A = 'Entity Id';

                            LOAD RecNo() as Row,

                              A

                            FROM

                            [$(vFileName)]

                            (ooxml, no labels, table is [Entity Rating Trend Report]);

                             

                            LET vRow = Peek('Row');

                             

                            LOAD F1 as [Entity Id],

                                 [2012Q3],

                                 [2012Q4],

                                 [2013Q1],

                                 [2013Q2],

                                 [2013Q3],

                                 [2013Q4],

                                 [2014Q1],

                                 [2014Q2],

                                 [2014Q3],

                                 [2014Q4],

                                 [2015Q1],

                                 [2015Q2],

                                 [2015Q3],

                                 [2015Q4],

                                 [2016Q1],

                                 [2016Q2]

                            FROM

                            [$(vFileName)]

                            (ooxml, embedded labels, header is $(vRow) lines, table is [Entity Rating Trend Report]);

                             

                             

                            Regardless of which row the Entity Id starts, you will be able to run the scrip without any error. Only catch here is that the FieldHeader (Entity Id) needs to be consistent across, else you are going to run into issues because that is the logic you would need to use to determine how much offset you need here.

                             

                            Attaching all the files for you to test out.

                             

                            Best,

                            Sunny