Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

malradi88
Contributor

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
Honored Contributor II

Re: Removing a row post-load

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

malradi88
Contributor

Re: Removing a row post-load

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
Honored Contributor II

Re: Removing a row post-load

LOAD

...

...

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

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

malradi88
Contributor

Re: Removing a row post-load

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
Honored Contributor II

Re: Removing a row post-load

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
Contributor

Re: Removing a row post-load

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
Contributor

Re: Removing a row post-load

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

Community Browser