Skip to main content
  • EN

I have two datasets:

1) DBF1 - It contains demand and patterns corresponding to a particular ID

2) DBF2 - It contains the factor corresponding to each PATTERN

I need to create a new column for each type and add the factor corresponding to PATTERN_ID, otherwise the default factor is 0.

The output result result would look as follows:

I tried using Feature Merger, but I felt that it would require multiple Feature Mergers to achieve the same.

I even tried Inline Querier but it does not permit more than one SQL statements.

Is there an alternative of doing the same

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1 d1

That should give you the right result.

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1 d1

That should give you the right result.

And to get the 0 values instead of the Null values for non matching patterns you can follow the InlineQuerier by a NullAttributeMapper.

 

 

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1  d1

That should give you the right result.

Agree. You can accomplish the goal with a single SQL statement. This example assigns 0 to the default factor, although a bit more complex.

 

select
    a.ID,
    a.DEMAND1,
    a.PATTERN1,
    case
        when b.FACTOR is null then 0
        else b.FACTOR
    end as PAT1_FACTOR,
    a.DEMAND2,
    a.PATTERN2,
    case
        when c.FACTOR is null then 0
        else c.FACTOR
    end as PAT2_FACTOR,
    a.DEMAND3,
    a.PATTERN3,
    case
        when d.FACTOR is null then 0
        else d.FACTOR
    end as PAT3_FACTOR,
    a.DEMAND4,
    a.PATTERN4,
    case
        when e.FACTOR is null then 0
        else e.FACTOR
    end as PAT4_FACTOR,
    a.DEMAND5,
    a.PATTERN5,
    case
        when f.FACTOR is null then 0
        else f.FACTOR
    end as PAT5_FACTOR
from DBF1 as a
left outer join DBF2 as b on b.PATTERN_ID = a.PATTERN1
left outer join DBF2 as c on c.PATTERN_ID = a.PATTERN2
left outer join DBF2 as d on d.PATTERN_ID = a.PATTERN3
left outer join DBF2 as e on e.PATTERN_ID = a.PATTERN4
left outer join DBF2 as f on f.PATTERN_ID = a.PATTERN5

 

Reply


Community Stats

30,185
Posts
114,572
Replies
37,848
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.