topic Re: Scenario Help in QlikView

Scenario Help

abhaysingh — Sat, 25 Jul 2015 10:02:10 GMT

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...

Re: Scenario Help

swuehl — Sat, 25 Jul 2015 10:09:24 GMT

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

Re: Scenario Help

sunny_talwar — Sat, 25 Jul 2015 11:51:01 GMT

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