Merging tables

[#1]

Trying to join these two tables.

Customer_FX:

NOCONCATENATE

LOAD

     FX_Key,

  Source,

     Market,

     Account,

     Customer,

     Segment,

     Currency,

     Month,

  Year,

     MonthYear,

     Month_Year_Currency,

     Actual,

     MonthID#,

     MonthYear#

RESIDENT Customer;

MergeAll_FX:                    

NoConcatenate

Load

   FX_Key,

   ThisFXRate,

   ThisFXMonthID#,

   ThisFXYear,

   ThisFXCurrency,

   ThisFXMTHYEAR#

Resident Currency_FX;

Now I'm trying to add table 2 field to the main Fact table Customer_FX:

left join (Customer_FX)

Load

  ThisFXRate

Resident MergeAll_FX

group by FX_Key;

Getting this error message:

Aggregation expressions required by GROUP BY clause

The error occurred here:

left join (Customer_FX) Load ThisFXRate Resident MergeAll_FX group by FX_Key

[robert_mika]

When using group you need to use aggregation function like

SUM,COUNT,MAX

like

Load

FX_Key,

SUM(ThisFXRate) as TotalFX

Resident MergeAll_FX

group by FX_Key;

[#3]

Thank you Robert, i need the field in its original state. Sum will change the value. Any ideas?

[#4]

The record count in the Fact table went from 50K to 19M  also. Not what I expected. (some merge ha? )

[robert_mika]

What are you trying to achieve?

Maybe like this?

left join (Customer_FX)

Load

FX_Key,

  ThisFXRate

Resident MergeAll_FX

Then the Customer table will be joined on your FX_Key

no need for group statement.

Then you can drop the MergeAll_FX table

As an alternative use AppplyMap

Don't join - use Applymap instead

Feeling Qlikngry?

How To /Missing Manual(25 articles)