10 Replies Latest reply: Mar 23, 2017 6:34 AM by Stefan Wühl RSS

    Nested Load Statement - Different Results Depending on KEEP placement

    Darren Hankinson

      Consider this and where the KEEP statement is. This produces a table with 2.2million rows which "seems" correct.

       

      If however I move the keep statement to the final load statement I get circa 750k rows.

       

      IN the UI this results in slightly different claim_counters.

       

      For the life of me I cannot think why this would be different. As you would expect placing the keep on the first load statement would simply mean that all rows would be dropped from T_CLAIM2 before the calculated fields were derived, whereas placing it last would mean the calculations would simply be done on all rows and then subsequently dropped.

       

      Any ideas?

      T_CLAIMS2:
      LOAD *,
      IF([Actual Date] = 'NA','$(vNaDesc)',IF(LEN([Actual Date])>2,'$(vClaimDesc)',IF(LEN([Measure Date])=0 OR [Measure Date] >=TODAY(),'$(vExpDesc)','$(vOsDesc)'))) AS [Measure Type]
      ;
      LOAD *,
      IF(LEN([Actual Date])>=2,[Actual Date],IF(PTYPE = 'IS Delivery' AND (msname = 'pre-production complete' OR msname = 'feasibility study required'),[Forecast Date],[Expected Date])) AS [Measure Date]
      ;
      INNER KEEP(D_PROJECT)
      LOAD
        msname,
        AUTONUMBER(msname,'%_MSID') AS %_MSID,
        %_PROJECTID,
        %_SITEID,
        [Expected Date],
        [Forecast Date],
        [Actual Date],
        PTYPE,
        TTYPE,
        %_TARGPROGID,
        1 AS #_CLAIM_COUNTER
      RESIDENT T_CLAIMS;
      DROP TABLE T_CLAIMS;