• Home
  • Forums
  • FME Form
  • Transformers
  • I have a text file with several rows relating to one unique record. How do I assign the unique record code relating to the first row to the related rows below. First field of the row is always 1 for the primary row. Related rows have a different numb
Question

I have a text file with several rows relating to one unique record. How do I assign the unique record code relating to the first row to the related rows below. First field of the row is always 1 for the primary row. Related rows have a different numb

ruth_h
Rank
Badge +3

Hi, I have a file with several lines that contain information about a unique record. However only line 01 has the unique information about the property. The remainder of the lines have further information about the property and start with different numbers. The row with the main information always starts with a 01. Subsequent rows have a different number.

 

Can someone please advise how I would add the unique reference in the example below in the second field in line 01 (19633154000) to all the rows below until you get to the next row with line 01. Many thanks.

 

Eg

01*19633154000*10870539000*0335**45-47****YORK ROAD

02*1*Ground*90

02*9*First*18

04*128

01*17163059000*7648136000*6955*WILKINSON BUSINESS PARK

02*1*First*38

02*3*First*0.00

02*4*First*Kitchen*1

05*10*0**0*+0

14 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

The way I do this is using an AttributeCreator set to handle adjacent feature attributes and a conditional value like you can do in Excel.

2022-04-01_05h11_49If previous row does not exist, groupId = 1.

ElseIf row begins with 01, groupId = previous groupId + 1.

Else, groupId = previous groupId.

 

Sample workspace attached.

takashi
Rank
Userlevel 2
Badge +17

Hi @ruth_h​ , a possible way is:

Separate the text lines into ones starting with "01" and others with the Tester (Preserve Feature Order: Across Output Ports).

On the flow for the text lines starting with "01*", extract the second field ("19633154000", "17163059000" in your example), and then save it as a global variable with the VariableSetter.

On the other flow, retrieve the global variable with the VarialeRetriever.

The attached screenshot illustrates a data flow example, assuming that the second field is always 11 characters. Be aware that the Preserve Feature Order parameter in the Tester should be set to "Across Output Ports" in order to keep the order of the text lines to be processed.wokflow-example

takashi
Rank
Userlevel 2
Badge +17

Hi @ruth_h​ , a possible way is:

Separate the text lines into ones starting with "01" and others with the Tester (Preserve Feature Order: Across Output Ports).

On the flow for the text lines starting with "01*", extract the second field ("19633154000", "17163059000" in your example), and then save it as a global variable with the VariableSetter.

On the other flow, retrieve the global variable with the VarialeRetriever.

The attached screenshot illustrates a data flow example, assuming that the second field is always 11 characters. Be aware that the Preserve Feature Order parameter in the Tester should be set to "Across Output Ports" in order to keep the order of the text lines to be processed.wokflow-example

This is another approach, inspired by @nielsgerrits​ 's answer.wokflow-example-2

ruth_h
Rank
Badge +3

Hi both, thank you so much for your help. Still struggling though. takashi - I have done this. However although the variablesetter has the id in, the variable receiver shows nothing in the id field. Please can you advise? Many thanks.

 

image 

image 

image

takashi
Rank
Userlevel 2
Badge +17

Hi both, thank you so much for your help. Still struggling though. takashi - I have done this. However although the variablesetter has the id in, the variable receiver shows nothing in the id field. Please can you advise? Many thanks.

 

image 

image 

image

I think the unexpected result is a side effect of the Feature Caching function. Check the Feature Caching off, then try again.

ruth_h
Rank
Badge +3

Hi takashi, unfortunately that didn't make any difference. I am a fairly basic user of FME, but I was wondering in this method how would it know to connect the lines below the one starting 01 with the 01 line with the unique reference. I was hoping it would magically work (it didn't) but I have to confess I don't understand how it would! Thanks Ruth

takashi
Rank
Userlevel 2
Badge +17

Hi takashi, unfortunately that didn't make any difference. I am a fairly basic user of FME, but I was wondering in this method how would it know to connect the lines below the one starting 01 with the 01 line with the unique reference. I was hoping it would magically work (it didn't) but I have to confess I don't understand how it would! Thanks Ruth

That's strange. Can you please share your workspace here?

ruth_h
Rank
Badge +3

Hi there, Here is the workspace and test file. Many thanks

1 Attachment
takashi
Rank
Userlevel 2
Badge +17

Thanks for sharing your workspace and sample data.

I ran your workspace and was able to get desired result shown in the screenshot. I think there is no problem.result

ruth_h
Rank
Badge +3

That's great thanks. Really weird - I closed it and opened and ran again and working for me too! Maybe I hadn't switched of the cache properly even though I thought I had. Many thanks for you help. Much appreciated.

jkr_wrk
Rank
Userlevel 3
Badge +18

That's great thanks. Really weird - I closed it and opened and ran again and working for me too! Maybe I hadn't switched of the cache properly even though I thought I had. Many thanks for you help. Much appreciated.

Because I never want to depend on feature order preservation on separate flows I prefer @nielsgerrits​ solution. FME has become better in preserving the order but If something slows down one flow it will break functionality.

Sample: Every Nth Feature (N=1)
ruth_h
Rank
Badge +3

Hi there, thanks for the input - unfortunately I didn't quite understand how to implement the suggestion from nielsgerrits so I didn't try that one.

jkr_wrk
Rank
Userlevel 3
Badge +18

Hi there, thanks for the input - unfortunately I didn't quite understand how to implement the suggestion from nielsgerrits so I didn't try that one.

create the variable that will hold the 'group' number. It can be empty for now.

Than use a AttributeCreator and set the checkbox for Adjacent Feature Attributes.

imageThen look at the start of your line. If it starts with the indicator for "New Feature", which is 01 in your case. Add +1 to the 'group' number of the Prior Feature for the current feature. Else use the 'group' number for the current feature.

imageThe conditional Settings:

imageThe @Value(feature[-1].featureIndex) is only available with the checkbox checked.

 

By the way: you could use reply on someone to keep the conversation together.

 

 

Sample: Every Nth Feature (N=1)
ruth_h
Rank
Badge +3

Hi jkr_da - Thank you very much for that. I will give it a go.

Reply


Community Stats

28,714
Posts
109,490
Replies
36,561
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.