Scenario Help

[abhaysingh]

HI all,

LOAD //[Customer Code],
   Quantity,
[Sale Price],

[BBE No],
[Invoice Number],
// [Invoice Date],
   [Quatity Case],
[Group Code],
[Item Desc],
sum([Custom Duty Interest]) as [Custom Duty Interest]
FROM

(qvd) Group by [Invoice Number];

while doing group by getting error

invalid expression

BulkSaleDetl:

LOAD

     Quantity,

     [Sale Price],

     [BBE No],

     [Invoice Number],

     [Quatity Case],

     [Group Code],

  [Item Desc],

  sum([Custom Duty Interest]) as [Custom Duty Interest]

FROM

(qvd) Group by [Invoice Number]

what is the error??

Actually I have two tables Main and Detail and Performing Join between them but My records are multiplying, so I m doing grouping to handle it.

Please suggest how I can handle it...

[swuehl]

When using a GROUP BY clause, all fields not stated in the clause, but appearing in the LOAD must be used within an aggregation function (or added to the GROUP BY field list.

  Quantity,

  [Sale Price],

  [BBE No],

[Quatity Case],

[Group Code],

[Item Desc],

Either aggregate, remove or add to GROUP BY

[sunny_talwar]

You can do do it this way:

Table:

LOAD //[Customer Code],
         Quantity,
          [Sale Price],
          [BBE No],
          [Invoice Number],
          // [Invoice Date],
          [Quatity Case],
          [Group Code],
          [Item Desc],

          [Custom Duty Interest]
FROM

(qvd);

Join (Table)

LOAD [Invoice Number],

          Sum([Custom Duty Interest]) as [Sum Custom Duty Interest]

Resident Table

Group by [Invoice Number];

DROP Field [Custom Duty Interest];

Rename Field [Sum Custom Duty Interest] as [Custom Duty Interest];

HTH

Best,

Sunny