Delete row of a table based on the content of a column

[RubenMoreno]

Hello,

I have loaded a table from an excel file and I would like to delete the rows in the table that contain in "Column A" the words "Out of scope".

How would be the coding to include in the Data Load Editor?

Thank you.

Best regards,
Rubén

[paulselousyoriz · Accepted solution]

Hello Rubén,

I presume you do not mean delete the row(s) from the Excel file, but instead delete (or not load) the row(s) from the Excel file into the table in QlikSense.

You can use the following code:

Table1:
LOAD
[Column A],
[Column B]
FROM [lib://AttachedFiles/Test99.xlsx]
(ooxml, embedded labels, table is Blad1)
Where [Column A] <> 'Out of scope';

If this solves your issue, please mark this post as Correct.

Regards,

Paul

[Kushal_Chawda]

May be you need to look for wildcard search.

If you want to filter the Data from Table, you can do like below

Data:
LOAD *
FROM YourQVD
where wildmatch(lower([Column A]),'*out of scope*');

If you want your data as it is and just want to filter the data in charts, then create flag in script and use that Flag in set analysis expression

Data:
LOAD *,
     if(wildmatch(lower([Column A]),'*out of scope*'),1,0) as Flag;
FROM YourQVD;

Now you can use this Flag in set analysis expression as eg. like below

=sum({<Flag={1}>}Value)