Question

Loop through attributes by specific id that matches column name

  • 21 October 2019
  • 5 replies
  • 0 views
freddie_kwak
Badge

Hello everybody,

 

I hope somebody can help me with the following challenge:

I have 2 datasets/dataflows in a workbench:

-1. MeldingData: One set contains registered data like roadnumber, type of animal, status, etc

-2. FormData: The other set contains the pulldown menu options from the iASSET application database where the value is shown in the first dataset.

 

The set MeldingData has colomn names like data.100003, data.100004, data.100024, etc. The number of the colomn from MeldingData matches the colomn id from FormData.

 

I want to replace the code in MeldingData with the matching value/label from FormData, so it makes the dataset more readable.

An example: the first row of MeldingData has value 05 in colomn data.100004. This means that this notification/call is made on roadnumber N373. Al the other colomns need to be processed simmilar.

 

I attatched the workbench and an excelfile with the MeldingData and FormData.

 

I hope you understand my explanation. If you need more information, please let me know.

 

 

Kind regards,

Freddie.

5 replies

arnovananrooij
Rank
Badge +4

Hi Freddie,

You could use an InlineQuerier to solve this. I have made a begin with the query to use. This query looks up labels for data.100003 and data.100004. Just add all the other columns the same way. The query to use is:

 

select "MeldingData"."id"

, "MeldingData"."latitude"

, "MeldingData"."longitude"

, "MeldingData"."pin_icon"

, "MeldingData"."locally_created"

, "MeldingData"."data.100003"

, "fd100003"."label" as "label.100003"

, "MeldingData"."data.100004"

, "fd100004"."label" as "label.100004"

from "MeldingData"

, "FormData" fd100003

, "FormData" fd100004

where "fd100003"."id" = 100003

and "MeldingData"."data.100003" = "fd100003"."value"

and "fd100004"."id" = 100004

and "MeldingData"."data.100004" = "fd100004"."value"

 

The workspace itself will be pretty easy:

Greetings

Arno

G
Badge +3

@freddie_kwak

Featuremerger solution. more generic(ish) and less typing ;)

 

Dynamci key Kwak.fmw

G
Badge +3

@freddie_kwak

 

Updated, added the not merged objects (in case you did not figure it out yourself, which is presumed)

31082-dynamci-key-kwak.fmw

Ouptutexample added.

Question;

Are integer values in the data. columns which are seperated by comma' s multiple values ?

 

freddie_kwak
Badge

@freddie_kwak

 

Updated, added the not merged objects (in case you did not figure it out yourself, which is presumed)

31082-dynamci-key-kwak.fmw

Ouptutexample added.

Question;

Are integer values in the data. columns which are seperated by comma' s multiple values ?

 

Hello Gio, sorry for my late reaction! The data. columns do not have multiple values. Always a single value. Thank you for your help! It works realy well! learned a new FME-skill! Thank you for that!

freddie_kwak
Badge

Hi Freddie,

You could use an InlineQuerier to solve this. I have made a begin with the query to use. This query looks up labels for data.100003 and data.100004. Just add all the other columns the same way. The query to use is:

 

select "MeldingData"."id"

, "MeldingData"."latitude"

, "MeldingData"."longitude"

, "MeldingData"."pin_icon"

, "MeldingData"."locally_created"

, "MeldingData"."data.100003"

, "fd100003"."label" as "label.100003"

, "MeldingData"."data.100004"

, "fd100004"."label" as "label.100004"

from "MeldingData"

, "FormData" fd100003

, "FormData" fd100004

where "fd100003"."id" = 100003

and "MeldingData"."data.100003" = "fd100003"."value"

and "fd100004"."id" = 100004

and "MeldingData"."data.100004" = "fd100004"."value"

 

The workspace itself will be pretty easy:

Greetings

Arno

Hello Arno, thank you for the solution! Also works very well! Sometimes difficult to understand for me, but good to know i can solve these kind of problems with the InlineQuerier! Thank you for your help!

Reply


Community Stats

28,688
Posts
109,431
Replies
36,542
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.