Problems using precedent load

[Anonymous]

I have a file with duplicates rows, then I load with distintct, but when I make a precedent load to do an aggregagate with the previous data it makes the aggregate with the dups rows....(the distinct doesn't have effect)

Is there any solution?

LOAD ciudad, producto, mes, sum(venta) as venta GROUP BY ciudad, producto, mes;

LOAD Distinct mes,

     ciudad,

     producto,

     venta

FROM

(ooxml, embedded labels, table is Hoja1);

Thanks

[puttemans]

I've not yet seen a group by clause in a preceding load. I would split the script in 2 different subsequent actions.

[SreeniJD]

Hi there,

Change the order of your LOAD statements, it will work

Thanks,

Sreeni

[Anonymous]

Nooo, I can't change load order

I need to eliminate duplicates before aggregate!!

If I aggregate with duplicates and after that I do the distinct it won't be the same....I have the dups in my aggregates rows,,,

[puttemans]

try the following :

100:

LOAD Distinct mes,

     ciudad,

     producto,

     venta as venta1

FROM

(ooxml, embedded labels, table is Hoja1);

noconcatenate

200:

LOAD ciudad,

     producto,

     mes,

     sum(venta1) as venta

RESIDENT 100

GROUP BY ciudad, producto, mes;

DROP TABLE 100;

[SreeniJD]

Hi Juan,

If possible, can you pls share the sample qvw with dummy data, I will try with a POC.

Sreeni

[Digvijay_Singh]

Like this - I think preceding load process one record at a time at all load levels, might be that is not allowing to remove distincts but not sure...

TableA:

Load Distinct * inline [

Prod, Sales

A, 100

A, 100

B, 50

C, 100

C, 100

C, 200

D, 200

D, 300 ];

TableB:

Load Prod, Sum(Sales) Resident TableA Group By Prod;

Drop table TableA;

[simenkg]

Distinct is the last action to be executed in a load. The easiest way to fix this is to split it  into two loads.

tempTable:

Load * from file;

FinalTable:

load ciudad, producto, mes, sum(venta) as venta resident tempTable GROUP BY ciudad, producto, mes;

drop table tempTable;

[tresesco]

This is now a known issue(or say, behavior), explained nicely by Barry here: DISTINCT can be deceiving - The Qlik Fix! The Qlik Fix!

Qlikview would generate a distinct output, no matter wherever(in the first preceding load or later) you use distinct keyword. To sort out this issue you have to use a resident load.