1 Reply Latest reply: Feb 5, 2017 9:15 AM by Evgeny Stuchalkin RSS

    Phantom entities from script calculation

    Evgeny Stuchalkin

      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:

       

      00cc5be0da.jpg

       

      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:

       

      90ee71a2e4.jpg

      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

       

      6df8fdc350.jpg

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

       

      What is wrong?