7 Replies Latest reply: Jun 30, 2017 11:03 AM by Mohammed Al Radi RSS

    Removing a row post-load

    Mohammed Al Radi

      Hello Qlik Community,

       

      I have just uploaded a data-set with 2 tables. Unfortunately, I forgot to remove the totals row for one of the tables - Is there a way of removing a row through the data load editor? I know I can just remove the row from the actual data-set and re-load, but am interested to know if there is a function that can do this and how.

       

      Thank you for your support!

       

      Best,

       

      Mohammed

        • Re: Removing a row post-load
          Andrea Gigliotti

          i think you can simple apply a where condition on the load statement.

            • Re: Removing a row post-load
              Mohammed Al Radi

              Thank you Andrea. Please excuse my ignorance but how and where would I add that 'where condition' in this script. I am trying to get rid of row 421 of the excel tab that the below load block is based on.

               

              [المتدربات و مقدمي الرعاية ]:

              CROSSTABLE ([التاريخ/ Date],[الدخل /Salary],27)

              LOAD

              [اسم المقدم رعاية -Name of Caregiver],

              [المتدربات و مقدمي الرعاية .Type/النوع],

              [المتدربات و مقدمي الرعاية .الفرع/Branch],

              [Gender/ الجنس],

              [المتدربات و مقدمي الرعاية .تاريخ الميلاد/Date of Birth],

              [المتدربات و مقدمي الرعاية .العنوان/Address],

              [المتدربات و مقدمي الرعاية .District/حي],

              [موبايل/Mobile number],

              [تليفون آخر/Other Telephone],

              [الحاله الأجتماعيه/Marital Status],

              [الموقف من التجنيد/Conscription Status],

              [الأمراض المزمنه/Chronic Illness],

              [Education/مستوى التعليم],

              [Specialisation/التخصص],

              [سنة التخرج/Graduation Date],

              [الوظيفه السابقه/Past Occupation],

              [سبب ترك العمل/Reason for Resignation],

              [كيف تعرفت علينا/How Did the Applicant Hear about Us],

              [Attended Training/حضرت التدريب],

              [المتدربات و مقدمي الرعاية .Start Date/تاريخ البدء],

              [المتدربات و مقدمي الرعاية .End Date/تاريخ الانتهاء],

              [Group Year/سنة المجموعة],

              [Group Number/رقم المجموعة],

              [درجات نظرى/Theory Grade],

              [درجات عملى/Practical Grade],

              [درجات ميدانى/Field Grade],

              [اجمالى درجات/Total Grade],

              [42005],

              [42036],

              [42064],

              [42095],

              [42125],

              [42156],

              [42186],

              [42217],

              [42248],

              [42278],

              [42309],

              [42339],

              [42370],

              [42401],

              [42430],

              [42461],

              [42491],

              [42522],

              [42552],

              [42583],

              [42614],

              [42644],

              [42675],

              [42705],

              [42736],

              [42767],

              [42795],

              [42826],

              [42856],

              [42887],

              [42917],

              [42948],

              [42979],

              [43009],

              [43040],

              [43070];

              LOAD

              [اسم/Name] AS [اسم المقدم رعاية -Name of Caregiver],

              [Type/النوع] AS [المتدربات و مقدمي الرعاية .Type/النوع],

              [الفرع/Branch] AS [المتدربات و مقدمي الرعاية .الفرع/Branch],

              [Gender/ الجنس],

              Date([تاريخ الميلاد/Date of Birth] ) AS [المتدربات و مقدمي الرعاية .تاريخ الميلاد/Date of Birth],

              [العنوان/Address] AS [المتدربات و مقدمي الرعاية .العنوان/Address],

              [District/حي] AS [المتدربات و مقدمي الرعاية .District/حي],

              [موبايل/Mobile number],

              [تليفون آخر/Other Telephone],

              [الحاله الأجتماعيه/Marital Status],

              [الموقف من التجنيد/Conscription Status],

              [الأمراض المزمنه/Chronic Illness],

              [Education/مستوى التعليم],

              [Specialisation/التخصص],

              [سنة التخرج/Graduation Date],

              [الوظيفه السابقه/Past Occupation],

              [سبب ترك العمل/Reason for Resignation],

              [كيف تعرفت علينا/How Did the Applicant Hear about Us],

              [Attended Training/حضرت التدريب],

              [Start Date/تاريخ البدء] AS [المتدربات و مقدمي الرعاية .Start Date/تاريخ البدء],

              Date(Date#([End Date/تاريخ الانتهاء], 'DD/MM/YYYY') ) AS [المتدربات و مقدمي الرعاية .End Date/تاريخ الانتهاء],

              [Group Year/سنة المجموعة],

              [Group Number/رقم المجموعة],

              [42005],

              [42036],

              [42064],

              [42095],

              [42125],

              [42156],

              [42186],

              [42217],

              [42248],

              [42278],

              [42309],

              [42339],

              [42370],

              [42401],

              [42430],

              [42461],

              [42491],

              [42522],

              [42552],

              [42583],

              [42614],

              [42644],

              [42675],

              [42705],

              [42736],

              [42767],

              [42795],

              [42826],

              [42856],

              [42887],

              [42917],

              [42948],

              [42979],

              [43009],

              [43040],

              [43070],

              [درجات نظرى/Theory Grade],

              [درجات عملى/Practical Grade],

              [درجات ميدانى/Field Grade],

              [اجمالى درجات/Total Grade]

              FROM [lib://TFY/TFY For Qlik Master Sheet FINAL Copy.xlsx]

              (ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]);

                • Re: Removing a row post-load
                  Andrea Gigliotti

                  LOAD

                  ...

                  ...

                  FROM [lib://TFY/TFY For Qlik Master Sheet FINAL Copy.xlsx]

                  (ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]) where RowNo() <> 421;

                    • Re: Removing a row post-load
                      Mohammed Al Radi

                      Thank you Andrea! Unfortunately it is still not working. There are no errors reported when I load data but the information from row 421 is still present. Please let me know if you have any other suggestions.

                       

                      Best,

                       

                      Mohammed

                        • Re: Removing a row post-load
                          Andrea Gigliotti

                          first try adding:

                          LOAD

                          ...

                          ...

                          RowNo() as RowNumber

                          FROM [lib://TFY/TFY For Qlik Master Sheet FINAL Copy.xlsx]

                          (ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]);

                           

                          and check if RowNumber 421 is actually the row to get rid.

                            • Re: Removing a row post-load
                              Mohammed Al Radi

                              Hi Andrea,

                               

                              It still isn't working I checked the excel file and it is definitely row 421 of the excel tab that the table represents. I tried doing 420 and 422 as well in case qlik counted the rows differently.

                               

                              My colleague posed a similar question and she was recommended an inline function which seems much too long for my circumstance. In any case I will delete the row from the sheet for now, but if you think of anything else please share

                               

                              Thank you for all your help.

                               

                              Best,

                               

                              Mohammed

                                • Re: Removing a row post-load
                                  Mohammed Al Radi

                                  Hi Andrea,

                                   

                                  I tried this:

                                   

                                  LOAD

                                  ...

                                  ...

                                   

                                  FROM [lib://TFY/TFY For Qlik Master Sheet FINAL Copy.xlsx]

                                  (ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]) Where [اسم/Name]<>'اجمالى';

                                   

                                  and it worked. So [اسم/Name] is the first column in the excel tab and 'اجمالى ' the name in that cell. It took out all of row 421