Preceding load

[jleberre]

Hello

There is something I'm missing regarding "preceding load":

Code example:

source:
NoConcatenate
load * Inline [
date,customer,country
01/01/2018,A,FR
01/02/2018,A,FR
01/03/2018,A,FR
01/04/2018,A,FR
01/01/2018,B,US
01/02/2018,B,US
01/03/2018,B,US
01/04/2018,B,US
01/01/2018,B,US
01/02/2018,B,US
01/03/2018,B,US
01/04/2018,B,US
];

result:
NoConcatenate
load *,AutoNumberHash256(customer,date) as id;
load Distinct date,customer,country Resident source order by customer, date;

drop table source;

As I load in a first time distinct records, I expect only 8 records in the result table. But it is not the case (12 records, including 4 duplicate records). And I don't understand why. Could someone explain why please?

Thanks!

[jaimeaguilar]

Hi, 

the reason why you're not getting the expected result is because you need to include the "distinct" statement in every level of preceding load. Otherwise you'll get 12 rows instead of 8. This may be counter-intuitive, but this is the way qlik works,

result:
NoConcatenate
load distinct *,AutoNumberHash256(customer,date) as id;
load Distinct date,customer,country Resident source order by customer, date;

best regards

[skamath1]

I do not know why but group by will work

result:
NoConcatenate
load date,customer,country,AutoNumberHash256(customer,date) as id;
load date,customer,country,Count(1) as dummy
Resident source
group by date,customer,country
order by customer, date;

drop table source;

[OmarBenSalem]

u don't need preceeding load in ur case:

source:
NoConcatenate
load * Inline [
date,customer,country
01/01/2018,A,FR
01/02/2018,A,FR
01/03/2018,A,FR
01/04/2018,A,FR
01/01/2018,B,US
01/02/2018,B,US
01/03/2018,B,US
01/04/2018,B,US
01/01/2018,B,US
01/02/2018,B,US
01/03/2018,B,US
01/04/2018,B,US
];

result:
NoConcatenate
//load *,AutoNumberHash256(customer,date) as id;
load Distinct date,customer,country,AutoNumberHash256(customer,date) as id Resident source order by customer, date;

drop table source;

result:

[jleberre]

I know, it is just an example