topic Re: Load script remove rows based on containing character in QlikView

Load script remove rows based on containing character

Strooprover — Mon, 16 Aug 2021 13:31:00 GMT

Hi all,

I have the below load script.
In the original table (bron) is a field named 'text' which contains a lot of information.
I replaced the line feeds with the character '//' and made in a second table a new row for each part of the string.

Now some of these new rows (called substring) contain the character '€' and some don't.
I would like to only load the rows which contain a € into my QlikView file.
Can anyone help me with a correct syntax for this?

Bron:
SELECT
field1,
REPLACE(REPLACE(text, CHAR(13), '//'), CHAR(10), '//') as text
FROM ....

temp:
load field1,
'Sub'&Autonumber(RowNo(),RecNo()) as ColNo,
SubField(text,'//') as substring
resident Bron;

Re: Load script remove rows based on containing character

Andrei_Cusnir — Wed, 18 Aug 2021 10:15:37 GMT

Hello, if my understanding, of your use case scenario, is correct, then you are trying to achieve something like this:

I am going to skip the first LOAD statement as it is simply taking the text field and replaces all the line feeds with '//' characters
After the first load statement, you have a data set looking like this:
However, when you LOAD the second statement in QlikView, you are getting the following outcome:
And the issue here is that some of the records don't have the € symbol and you would like to remove them.

If my understanding was correct, there are 2 possible solutions to this issue:

Solution A:
- Modify the second LOAD statement to look like this:

Temp:
LOAD Data,
'Sub' & Autonumber(RowNo()&RecNo()) as ColNo,
SubField(Data,'//', 2) as substring
Resident Table1;

As you can see I have added the number 2 as the third argument in the function SubField(...). The SubField(...) function will break the string into multiple parts by using '//' as delimiter and will take the 2nd part only. So the results after the load is complete will be:

As you can see only the records that contain € symbol under substring field are loaded. However, this solution is only ideal under the following circumstances:

Format of the original data is [DATA]//€[Number]

This will NOT work under other circumstances, such as:

Format of the original data [DATA]//[DATA]//€[Number]
Format of the original data [DATA]//€[Number]//€[Number]//€[Number]
etc.

Therefore there is another solution for this issue, which is:

Solution B:
- Add another temp load statement that will load only the records with € from the previous loaded data and then drop the old temp table.

The new statements should look like this:

Temp:
LOAD Data,
'Sub' & Autonumber(RowNo()&RecNo()) as ColNo,
SubField(Data,'//') as substring
Resident Table1;

Temp2:
Noconcatenate
LOAD

Data,
ColNo,
substring
Resident Temp
WHERE WildMatch(substring, '*€*') > 0;

DROP Table Temp;

The second LOAD statement stays as it is.
You add another LOAD statement, to load the data from the first Temp table but only where the substring field contains € symbol.
Then you have to drop the first Temp table to avoid having your memory overloaded.

This will have the following result:

As you can see, you are left with Temp2 table that only contains records where substring has € symbol

I hope that this information has helped you resolve the issue.

Re: Load script remove rows based on containing character

MayilVahanan — Wed, 18 Aug 2021 10:31:51 GMT

Try with @Andrei_Cusnir approach or, instead of resident load, you can use preceding load also like below

temp:

Load * where wildmatch(substring, '*€*') > 0;
load field1,
'Sub'&Autonumber(RowNo(),RecNo()) as ColNo,
SubField(text,'//') as substring
resident Bron;

Re: Load script remove rows based on containing character

Strooprover — Thu, 19 Aug 2021 07:33:04 GMT

Thank you very much for your very comprehensive example.
It works like a charm!