Load script remove rows based on containing character

[Strooprover]

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;

[Andrei_Cusnir · Accepted solution]

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

1. 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
2. After the first load statement, you have a data set looking like this:
3. 
4. However, when you LOAD the second statement in QlikView, you are getting the following outcome:
5. 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.

[MayilVahanan]

Hi

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;

[Strooprover]

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