Announcements
cancel
Showing results for
Did you mean:
Specialist

## Group by problem

Hello,

I´ve trying to get sum aggregation in the Load Script but without results. In the excel File appears the final results and What I want and my final Output.

This is my scritpt: Load well but appears another numbers in the field.

NoConcatenate

Temp_Can_lao2:

Field1,

Date,

Name,

Sum( Distinct [Field 1]) as [Field 1]),

Country

Resident Temp_Can_lao1

Group By Field1,Date,Name,Country;

Drop Table Temp_Can_lao1;

Thanks!

1 Solution

Accepted Solutions
Anonymous
Not applicable

Hi Pablo,

If I'm understanding you correctly, you are just looking to get the aggregated sum by Field1, Date, and Name, correct?

Table1:

Field1,

Floor(Date) as Date,

Field2,

Name

FROM sourcefile.xls;

Table2:

Field1,

Sum(Field2) AS SumTotal

RESIDENT Table1

GROUP BY Field1,Name,Date;

Here you need to use Floor(Date) so that you get a whole number, without decimals. Otherwise you'd be grouping the date down to the last decimal place which will not work.

I hope that helps!

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

13 Replies
Anonymous
Not applicable

Hi Pablo,

If I'm understanding you correctly, you are just looking to get the aggregated sum by Field1, Date, and Name, correct?

Table1:

Field1,

Floor(Date) as Date,

Field2,

Name

FROM sourcefile.xls;

Table2:

Field1,

Sum(Field2) AS SumTotal

RESIDENT Table1

GROUP BY Field1,Name,Date;

Here you need to use Floor(Date) so that you get a whole number, without decimals. Otherwise you'd be grouping the date down to the last decimal place which will not work.

I hope that helps!

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Specialist
Author

Hi Serina,

I had already been grouped so but with this formula date(Left(Date,10),'DD/MM/YYYY') This gave me just date format and no TimeStamp, the problem that my formula and your method reach the same result, and that is a very high data should show 750 and shows 73,436,650 ... I do not know why the number ... When I add a Distinct to the sum, I lower the result to 300 ...

Specialist
Author

Look,

Same Script;

First Table has Sum(Distinct [Field1])

2nd Table Has Sum([Field1])

Without Distinct The number increase a lot...

What can be?

Partner - Creator II

Try this

t1:

Date,

Field2,

Name

FROM

(ooxml, embedded labels, table is Sheet1);

t2:

sum(Field2) as Field2

Resident t1

Group by Field1;

drop table t1;

Specialist
Author

Hi,

But I need the Date,

Floor(Date) as Date, as said

Gives:

Field1

1          73.436.650

2          100

I don´t know why the register 1 increase the number too much!!

Anonymous
Not applicable

Hi Pablo, do you have multiples table associated in your data model? Are you able to share what your data model looks like?

Specialist
Author

Hi Serina,

Floor(FechayHora) as Fecha      **********    here i do your line script ************

Its just a Historical QVD from sep2017 - apr2018

Where Date(FechayHora) >= '10/02/2018' And Date(FechayHora) < '11/02/2018';

So brings me just 10/02/2018. After that i do this:

NoConcatenate

Temp_1: //QVD Resident (Toda la data consolidada)

Branch,

Fecha,

Nombre,

Sum(Field) as Field,

Pais

Resident Temp_2

Group By Branch,Nombre,Pais,Fecha;

Drop Table Temp_1;

Must bring

Branch 1 750

Branch 2 100

Actually brings:

Branch 1 73.436.650 (Weird)

Branch 2 100 (Correct)

Specialist
Author

Look,

I just load the first table. Then I create a Pivot table where i Put

Sum(Field1)

Total = 73.436.750 (so weird)

If I do Sum(Distinct Field1)

Total = 850 (that is Correct)

Why sum Increase too much???

When a try to put sum(Distinct Field1) as Field1 in the group by table,

The result

Total = 400 (Incorrect)

Champion III

Can you share your script where you getting this 73.435.650? I mean expression?

Try like

ExcelLd:
Date#(Date) AS Date,
Field2,
Name
FROM

(
ooxml, embedded labels, table is [Group By]);

Final: