Group by problem

[pgalvezt]

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:

LOAD

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!

[Anonymous · Accepted solution]

Hi Pablo,

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

Then you'd want to write your load script as:

Table1:

LOAD

     Field1,

     Floor(Date) as Date,

     Field2,

     Name

FROM sourcefile.xls;

Table2:

LOAD

     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.

[pgalvezt]

Hi Serina,

Thank you for your reply:

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

[pgalvezt]

Look,

Same Script;

First Table has Sum(Distinct [Field1])

2nd Table Has Sum([Field1])

Without Distinct The number increase a lot...

What can be?

[vishalarote]

Try this

t1:

LOAD Field1,

     Date,

     Field2,

     Name

FROM

(ooxml, embedded labels, table is Sheet1);

t2:

load Field1,

sum(Field2) as Field2

Resident t1

Group by Field1;

drop table t1;

[pgalvezt]

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]

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

[pgalvezt]

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)

LOAD

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)

[pgalvezt]

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)

[vishsaggi]

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

Try like

ExcelLd:
LOAD Field1, Date,Field2, Name, DateField;
LOAD *, Num#(Date) AS DateField;
LOAD Field1,
Date#(Date) AS Date,
Field2,
Name
FROM

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


Final:
LOAD
Date(DateField, 'MM/DD/YYYY hh:mm:ss') As DateField1,
Sum(Field2) as Value
Resident ExcelLd
Group By DateField;
 
Drop Field DateField;