topic Re: Problems using precedent load in QlikView

Problems using precedent load

Anonymous — Thu, 24 Sep 2015 08:59:35 GMT

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

Re: Problems using precedent load

puttemans — Thu, 24 Sep 2015 09:13:52 GMT

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

Re: Problems using precedent load

SreeniJD — Thu, 24 Sep 2015 09:18:44 GMT

Hi there,

Change the order of your LOAD statements, it will work

Thanks,

Sreeni

Re: Problems using precedent load

Anonymous — Thu, 24 Sep 2015 09:24:36 GMT

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

Re: Problems using precedent load

puttemans — Thu, 24 Sep 2015 09:29:51 GMT

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;

Re: Problems using precedent load

SreeniJD — Thu, 24 Sep 2015 09:30:28 GMT

Hi Juan,

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

Sreeni

Re: Problems using precedent load

Digvijay_Singh — Thu, 24 Sep 2015 09:32:44 GMT

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

B, 50

C, 100

C, 200

D, 200

D, 300 ];

TableB:

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

Drop table TableA;

Re: Problems using precedent load

simenkg — Thu, 24 Sep 2015 09:46:49 GMT

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;

Re: Problems using precedent load

tresB — Thu, 24 Sep 2015 09:53:37 GMT

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.