Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
i think you can simple apply a where condition on the load statement.
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 [المتدربات و مقدمي الرعاية ]);
LOAD
...
...
FROM [lib://TFY/TFY For Qlik Master Sheet FINAL Copy.xlsx]
(ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]) where RowNo() <> 421;
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
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.
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
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