Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
malradi88
Creator II
Creator II

Removing a row post-load

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

7 Replies
agigliotti
Partner - Champion
Partner - Champion

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

malradi88
Creator II
Creator II
Author

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 [المتدربات و مقدمي الرعاية ]);

agigliotti
Partner - Champion
Partner - Champion

LOAD

...

...

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

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

malradi88
Creator II
Creator II
Author

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

agigliotti
Partner - Champion
Partner - Champion

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.

malradi88
Creator II
Creator II
Author

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

malradi88
Creator II
Creator II
Author

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