topic Merging tables in App Development

Merging tables

Wed, 13 Jul 2016 21:03:49 GMT

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

Re: Merging tables

robert_mika — Wed, 13 Jul 2016 22:03:21 GMT

When using group you need to use aggregation function like

SUM,COUNT,MAX

Load

FX_Key,

SUM(ThisFXRate) as TotalFX

Resident MergeAll_FX

group by FX_Key;

Re: Merging tables

Thu, 14 Jul 2016 00:12:35 GMT

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

Re: Merging tables

Thu, 14 Jul 2016 01:07:05 GMT

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

Re: Merging tables

robert_mika — Thu, 14 Jul 2016 09:05:40 GMT

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)