Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Need help with Sum..Group by

I have two tables right now, one of them is like this:

IDQnt
15
26
3

7

And the other is like this:

ID

Code

1

A

2

B
3B

I need to sum Qnt grouping by code (B would be 13 for example, since 6 and 7 are part of B), but when I try to do this, while I get the right numbers on B, A is returning me 10 instead of 5.

This is the script that I'm using:

Temp_Table:

LOAD ID,

          Qnt,

FROM Table_A.xlsx (ooxml, no labels);

left join

LOAD ID,

          Code

FROM Table_B.xlsx (ooxml, embedded labels);

Final_Table

LOAD

    Code

    Sum(Qnt) as Result

Resident Temp_Table Group By Code;

Drop Table Temp_Table;

What am I doing wrong? Thank you in advance for the help.

8 Replies
trdandamudi
Honored Contributor

Re: Need help with Sum..Group by

You need to drop the Temp_Table...

Temp_Table:

LOAD ID,

          Qnt,

FROM Table_A.xlsx (ooxml, no labels);

left join

LOAD ID,

          Code

FROM Table_B.xlsx (ooxml, embedded labels);

Final_Table:

LOAD

    Code,

    Sum(Qnt) as Result

Resident Temp_Table Group By Code;

Drop Table Temp_Table;

Not applicable

Re: Need help with Sum..Group by

I forgot to mention I've dropped the first table after the sum, it does sum anyway though. What else could it be?

Thank you

MVP
MVP

Re: Need help with Sum..Group by

try without join (add a chart with Code and sum(Qnt))

what's the output?

Temp_Table:

LOAD ID,

          Qnt,

FROM Table_A.xlsx (ooxml, no labels);

Temp_Table1:

LOAD ID,

          Code

FROM Table_B.xlsx (ooxml, embedded labels);

trdandamudi
Honored Contributor

Re: Need help with Sum..Group by

I am getting the correct results as you expected. Please take a look at the attached file..

Not applicable

Re: Need help with Sum..Group by

I can't sum since they are on different tables now,

Field not found <Code>.

Any clue?

Thank you

Not applicable

Re: Need help with Sum..Group by

I can't open, I'm a free user. Could you please post the script?

MVP
MVP

Re: Need help with Sum..Group by

I said in a chart. Code is a field of one of your source excel.

Anyway, could you post the excel?

Maybe you have some duplicated ID in both the excel.

trdandamudi
Honored Contributor

Re: Need help with Sum..Group by

Here is the code:

Temp_Table:

Load * Inline [

ID,Qnt

1,5

2,6

3,7

];

Left join (Temp_Table)

Load * Inline [

ID, Code

1,A

2,B

3,B

];

Final_Table:

Load

  Code,

  Sum(Qnt) as Result

Resident Temp_Table

Group By Code;

Drop Table Temp_Table;

Community Browser