Phantom entities from script calculation

[evgeniystuchalk]

Hello! I have a task: make some math-calculated fields in load script. First, i'm loading sourse table:

Leads:

LOAD

    applymap('DomainMap',i_domainID,null()) as domain,

    i_leadID,

    i_contactID,

    i_companyID,

    s_name,

    i_sum,

    i_pipelineID,

    i_statusID,

    i_dateCreate,

    i_lastModified,

    date(floor(ConvertToLocalTime(25569 + (`i_dateCreate` / 86400), 'GMT+03:00'),1)) as [День создания],

    date(floor(ConvertToLocalTime(25569 + (`i_dateClose` / 86400), 'GMT+03:00'),1)) as [День закрытия],

    date(monthstart(date(floor(ConvertToLocalTime(25569 + (`i_dateClose` / 86400), 'GMT+03:00'),1))),'MMM YY') as [Месяц закрытия],

    i_dateClose,

    ApplyMap('UsersMap',i_domainID&i_responsibleUserID,null()) as user,

    s_tags

FROM [lib://QVD (win-lng2sl4osbd_qlik.acc)/amoCRM\leads.qvd] (qvd) where i_dateCreate>=$(vPastMonth) or i_dateClose>=$(vPastMonth);

after that i've create control table to confirm my load is correct. This is control line, that displayed than current manager have one closed deal on 20 000 on feb-2017:

After that i'm loading table with aggregation:

SalesStat:

Load

domain,

user,

count(i_leadID),

count(DISTINCT i_leadID),

sum(i_sum) as ThisMonthSum

Resident Leads  where i_statusID=142 and i_dateClose>=$(vThisMonth) group by domain, user;

drop table Leads;

As result i'm espect to see same info, but in number format. But it shows me results, multiplied on 3:

i'm created special field in load script, that counts amount of deal id: distinct and no distinct. And we see, that one closed deal for this managers counts three times. I cant get what wrong with my formula.

Additional info:

All managers for this domain have multiplied result. there is three managers total in this domain

but in another domains different between source data and calculation may be another.

What is wrong?

[evgeniystuchalk · Accepted solution]

omg, stupid error: source data was contains doubles