filter and substring

Hello

I think this is very simple question, but i am unable to find simple way to do. 

1.)  I have millions of data like below.

Rec_data Inst_id
10212003 2003-9472
3022004 T10212
142005 2008-492
23582008 T15201

i need right four digits form "Rec_date". we have "substring" in String manipulation node but it will work for same length of string.

2.) I need without "T" series data Which means "Inst_id" contains "-". Already i used Row Filter node but i dint find any use pattern matching.

I need output like below

Rec_data Inst_id
2003 2003-9472
2004 2008-492
2005  
2008  

Thanks in advance.

Hi,

1. In the String Manipulation node you can also combine different functions to obtain what you want. You your case, you can extract the 4 rightmost characters from the Rec_data column with this expression:

substr($Rec_data$, length($Rec_data$) - 4, 4)

Basically it is calculating the length of the string, subtracting 4 from it to define the start position, then extracting 4 characters. Note that there is no check to whether the string in Rec_data has a valid number of characters so you may get an error if the this is the case.

2. In order to use wild card matching in the Row Filter node, you need to tick the contains wild cards option. To exclude all rows where Inst_Id starts with a "T", you can use the Exclude rows by attribute value option from the left list, then select Inst_Id as the column to test, next enter T* as the Use pattern matching string and finally remember to tick the contains wild cards option. T* tells the node the exclude all rows starting with a "T" no matter of what is after it.

Hope this helps!

Cheers,
Marco.

1 Like

Hello,

I have data like below, i need to create row based on two columns data.

Year Min_value Max_value
2004 1 6
2005 3 5
2006 -1 7
2011 -2 671
2016 148 5766

so here i need a column start with "1" for every year upto it reaches to Max_value like below.

Year Min_value MAx_value Rowcount
2004 1 6 1
2004 1 6 2
2004 1 6 3
2004 1 6 4
2004 1 6 5
2004 1 6 6
2005 3 5 1
2005 3 5 2
2005 3 5 3
2005 3 5 4
2005 3 5 5
2006 -1 7 1
2006 -1 7 2
2006 -1 7 3
2006 -1 7 4

Please help me out.

Thanks in advance.

how could i do this:

1234567
4567
567894

i need this:

123456
456
56789

I need to subtract a digit from the end

You can use this:

substr($column1$, 0 , length($column1$) -1 )

1 Like

Hi @marco_ghislanzoni
It worked perfectly, thanks

1 Like