Script Error

[ba11]

Hello,

Below is my script:

// Load the Account Period table

ACCOUNT_PERIOD_TEMP:

LOAD 

1 AS LINE_NO,

(ACCT_YEAR-1) & '/' & RIGHT(ACCT_YEAR,2) AS ACCT_YEAR,

    'P' & NUM(ACCT_PERIOD, '00') & ' ' & PICK(ACCT_PERIOD, 'Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct') AS ACCT_PERIOD,

    BEGIN_DATE,

    END_DATE

FROM $(QVDLOCATION)ACCOUNT_PERIOD.QVD (QVD)

WHERE ACCT_YEAR >= '2001';

// Load the Order Changes table

ORDER_CHANGES_TEMP:

LOAD 

1 AS LINE_NO,

    JoinCUST_ORDER_LINE_ID,

    ORDER_CHANGES.DAILY_MOVEMENT,

ORDER_CHANGES.WEEKLY_MOVEMENT,

ORDER_CHANGES.PERIOD_MOVEMENT

RESIDENT ORDER_CHANGES;

// Join Account Period with Order Changes to assign each order change to its corresponding period

LEFT JOIN (ORDER_CHANGES_TEMP)

LOAD

LINE_NO,

    ACCT_YEAR & '-' & LEFT(ACCT_PERIOD,3) AS PERIOD,

    ACCT_YEAR as YEAR,

    BEGIN_DATE,

    END_DATE

RESIDENT ACCOUNT_PERIOD_TEMP;

QUALIFY *;

// Calculate the yearly movement for each period

ORDER_MOVEMENT:

LOAD 

JoinCUST_ORDER_LINE_ID,

    PERIOD,

    YEAR,

    SUM(ORDER_CHANGES.DAILY_MOVEMENT) AS YEARLY_DAILY_MOVEMENT,

    SUM(ORDER_CHANGES.WEEKLY_MOVEMENT) AS YEARLY_WEEKLY_MOVEMENT,

    SUM(ORDER_CHANGES.PERIOD_MOVEMENT) AS YEARLY_PERIOD_MOVEMENT

RESIDENT ORDER_CHANGES_TEMP

GROUP BY PERIOD, YEAR;

// Drop unnecessary tables

DROP TABLE ACCOUNT_PERIOD_TEMP;

DROP TABLE ORDER_CHANGES_TEMP;

It keeps coming up with 'Invalid Expression' error when loading the ORDER_MOVEMENT table.

Anyone know why that is?

Thank you. 

[martinpohl]

check your script.

I think the line JoinCUST_ORDER_LINE_ID, is wrong.

Regards

Martin

[LRuCelver]

As @martinpohl already pointed out, there is a missing space or linebreak after the join statement.

The line 

FROM $(QVDLOCATION)ACCOUNT_PERIOD.QVD (QVD)

might throw an error if the QVDLOCATION variable contains a space. To make sure everything is correctly interpreted I would enclose the path in either square brackets or quotation marks:

FROM [$(QVDLOCATION)ACCOUNT_PERIOD.QVD] (QVD)

[qv_testing · Accepted solution]

add column in group by JoinCUST_ORDER_LINE_ID

ORDER_MOVEMENT:

LOAD 

JoinCUST_ORDER_LINE_ID,

    PERIOD,

    YEAR,

    SUM(ORDER_CHANGES.DAILY_MOVEMENT) AS YEARLY_DAILY_MOVEMENT,

    SUM(ORDER_CHANGES.WEEKLY_MOVEMENT) AS YEARLY_WEEKLY_MOVEMENT,

    SUM(ORDER_CHANGES.PERIOD_MOVEMENT) AS YEARLY_PERIOD_MOVEMENT

RESIDENT ORDER_CHANGES_TEMP

GROUP BY PERIOD, YEAR, JoinCUST_ORDER_LINE_ID;

[ba11]

Thank you everyone, it does work without the Join field but I need it in my model to join the table to others. It says memory exceeded when I tried grouping by the Join field as well

[qv_testing]

You don't need a group by if these are written in an expression

    SUM(ORDER_CHANGES.DAILY_MOVEMENT) AS YEARLY_DAILY_MOVEMENT,

    SUM(ORDER_CHANGES.WEEKLY_MOVEMENT) AS YEARLY_WEEKLY_MOVEMENT,

    SUM(ORDER_CHANGES.PERIOD_MOVEMENT) AS YEARLY_PERIOD_MOVEMENT

[jochem_zw]

Add this field in the group by:

JoinCUST_ORDER_LINE_ID