topic Re: Group by problem in QlikView

Group by problem

pgalvezt — Fri, 27 Apr 2018 20:19:51 GMT

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!

Re: Group by problem

Anonymous — Fri, 27 Apr 2018 20:48:39 GMT

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.

Re: Group by problem

pgalvezt — Fri, 27 Apr 2018 21:24:22 GMT

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

Re: Group by problem

pgalvezt — Fri, 27 Apr 2018 21:38:55 GMT

Look,

Same Script;

First Table has Sum(Distinct [Field1])

2nd Table Has Sum([Field1])

Without Distinct The number increase a lot...

What can be?

Re: Group by problem

vishalarote — Mon, 30 Apr 2018 05:02:06 GMT

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;

Re: Group by problem

pgalvezt — Mon, 30 Apr 2018 15:02:55 GMT

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

Re: Group by problem

Anonymous — Mon, 30 Apr 2018 15:06:01 GMT

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

Re: Group by problem

pgalvezt — Mon, 30 Apr 2018 15:16:36 GMT

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)

Re: Group by problem

pgalvezt — Mon, 30 Apr 2018 15:43:06 GMT

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)

Re: Group by problem

vishsaggi — Mon, 30 Apr 2018 16:07:40 GMT

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;

Re: Group by problem

pgalvezt — Mon, 30 Apr 2018 16:44:15 GMT

Here is my script;

Temp_1:

LOAD

Branch,

FechayHora,

Nombre,

Tic,

Pais

FROM

[..\Qvd\MC\MCLAT.qvd] (qvd)

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

Re: Group by problem

Anonymous — Mon, 30 Apr 2018 16:56:24 GMT

Can post your entire load script? I'm a little confused, because it looks like you did a Resident load from Temp_2 in your reply above, but I don't see the load script for Temp_2.

If possible, can you also screenshot a preview of your table data?

Re: Group by problem

pgalvezt — Mon, 30 Apr 2018 17:06:24 GMT

This my script without resident and group by... Just the first table:

Temp_1:

LOAD

Branch,

FechayHora,

Nombre,

Tic,

Pais

FROM

[..\Qvd\MC\MCLAT.qvd] (qvd)

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

Here I bring 10/02/2018... If I do a pivot table Sum(Tic) Gives 73.436.650.

If I do Sum (Distinct Tic)) Gives 850 (Correct Output)...

After this i want to group.

Re: Group by problem

sasiparupudi1 — Mon, 30 Apr 2018 17:38:02 GMT

At what level is 850 correct ? at the Branch level?.. may be post some sample data with the expected outcome..

or may be try a left join?

Temp_1:

LOAD

Branch,

FechayHora,

Nombre,

Tic,

Pais

FROM

[..\Qvd\MC\MCLAT.qvd] (qvd)

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

Left Join(Temp_1)

Load

Distinct

Branch,

Sum(Tic)

Resident Temp_1

Group By Branch

;