Create a filtered new column in script

[simonegabrielli]

Hi there,

I am struggling with a problem but i believe it has a simple solution.

I have two table: [Year Code] (that contains the Year ID: Act_2018, Act_2019, etc....) and [Year Description] (that contains the informations about the years: 'Actual 2018', 'Actual 2019', etc....). I'm trying to create a new table, called [Year], which consists only of some fields present in the previous tables, that is 'Actual 2018' and 'Actual 2019'.

my actual script is:

Scenario:

LOAD
"Year Code",
"Year Description"
FROM [lib://.................);

thanks for the help.

[tm_burgers · Accepted solution]

Try:

[Year]:

noConcatenate LOAD

   [Year Code],

   [Year Description]

Resident  TableYear

group by [Year Code];

Or are your two tables not associated?

In that case, you need to create a KeyMatch field between them,

like :

right([Year Code],4) as YearKeyField

&

right([Year Description],4) as YearKeyField

then join the tables together like:

[Year]:

noConcatenate LOAD

   YearKeyField,

   [Year Code]

Resident  TableYearCodes

group by [YearKeyField];

left join (Year)

LOAD

    YearKeyField,

   [Year Description]

Resident  TableYearDescriptions

group by [YearKeyField];