Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
omg, stupid error: source data was contains doubles
omg, stupid error: source data was contains doubles