Skip to main content
Solved

Using IN command in InlineQuerier

A

I have two datasets that I am currently working on:

1) DBF1

2) DBF2

I need to lookup for Description (in DBF1) in DB2 within the columns I1 to I8 and

>> to return the corresponding DMA to Inflow field if a match is found OR

>> to display a space if no match found

The result would look as follows:

Currently, I am using the InlineQuerier with IN statement, but it truncates the result when there is no match. The NOT IN command displayed irrelevant results as well.

Please suggest a solution which may be SQL or Non SQL based.

Best answer by courtney_m

I was able to get this to work using an InlineQuerier that has 2 output ports. The DMA port outputs all "matched" records, and the DBF1 port outputs all records from DBF1. I used the following SQL statement for the DMA port:

SELECT "DMA","Description","ID"

FROM "DBF1" JOIN "DBF2"

ON "Description" IN ("I1","I2","I3","I4","I5","I6","I7","I8")

Then I used a FeatureMerger (DBF1 = Requestor, DMA = Supplier) that joined on Description. I connected the Merged port AND the UnmergedRequestor port to an AttributeManager (this gives me 1 stream with my 12 original records) - In the AttributeManager, I created the field Inflow, and set it to DMA, if DMA has a value. Otherwise, Inflow is set to a space. Then I removed the DMA field.

This is the output I got:

Here is a screenshot of the entire workspace:

And a screenshot of the AttributeManager properties:

I have also attached the workspace, in case you want to look at it.

Originally, I was going to output the "non-merged" features through another port of the InLineQuerier, but I also was unable to get the NOT IN statement to work successfully. This process is a little clunkier, but it works.

-Courtney

View original
Did this help you find an answer to your question?

2 replies

courtney_m
Contributor
Forum|alt.badge.img+5
  • courtney_mContributor
  • Contributor
  • Best Answer
  • August 17, 2017

I was able to get this to work using an InlineQuerier that has 2 output ports. The DMA port outputs all "matched" records, and the DBF1 port outputs all records from DBF1. I used the following SQL statement for the DMA port:

SELECT "DMA","Description","ID"

FROM "DBF1" JOIN "DBF2"

ON "Description" IN ("I1","I2","I3","I4","I5","I6","I7","I8")

Then I used a FeatureMerger (DBF1 = Requestor, DMA = Supplier) that joined on Description. I connected the Merged port AND the UnmergedRequestor port to an AttributeManager (this gives me 1 stream with my 12 original records) - In the AttributeManager, I created the field Inflow, and set it to DMA, if DMA has a value. Otherwise, Inflow is set to a space. Then I removed the DMA field.

This is the output I got:

Here is a screenshot of the entire workspace:

And a screenshot of the AttributeManager properties:

I have also attached the workspace, in case you want to look at it.

Originally, I was going to output the "non-merged" features through another port of the InLineQuerier, but I also was unable to get the NOT IN statement to work successfully. This process is a little clunkier, but it works.

-Courtney

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • August 17, 2017

This SQL statement could also be a possible way.

select
    a.*,
    case
        when b.DMA is null then ''
        else b.DMA
    end as Inflow
from DBF1 as a
left outer join (select * from (
    select DMA, l1 as Description from DBF2
    union all select DMA, l2 from DBF2
    union all select DMA, l3 from DBF2
    union all select DMA, l4 from DBF2
    union all select DMA, l5 from DBF2
    union all select DMA, l6 from DBF2
    union all select DMA, l7 from DBF2
    union all select DMA, l8 from DBF2
) where Description <> '') as b
on b.Description = a.Description

Alternatively, this Non SQL based workflow could work as well.

0684Q00000ArJtoQAF.png

Reply


Helpful Members This Week

Community Stats

30,929
Posts
117,224
Replies
38,734
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.