Order by in FOR Loop doesn't appear to be working

[triciagdaly]

In the script below, I am reading in all distinct suppliers, and for each supplier there are multiple effective date periods (which reflect cost roll periods).  The goal is to assign a number to each supplier and cost roll date.  However, in the table "supp cost rolls', it doesn't appear to be ordering them in EFFECTIVE_DATE sequence.  Below is the script and the output from the destination table Temp_skey.

fg_cost_elements:

LOAD SUPPLIER_ID,
     TEXT(ITEM_NUMBER) AS ITEM,
     MATERIAL_COST,
     LABOR_COST,
     PROFIT,
     MATERIAL_MARKUP,
     TEST,
     TOTAL_COST,
     EFFECTIVE_DATE,
     EXPIRATION_DATE,
     CURRENCY,
     COMPANY,
     text(SUPPLIER_ID)&'-'&text(COMPANY) as _s_key,
     _s_item_comp_key,
     text(SUPPLIER_ID)&'-'&text(ITEM_NUMBER)&'-'&date(EFFECTIVE_DATE) as _fg_key,
     text(SUPPLIER_ID)&'-'&text(ITEM_NUMBER) as _s_item_key,
     text(SUPPLIER_ID)&'-'&date(EFFECTIVE_DATE)&'-'&text(COMPANY) as _s_effdte_key,
     if(isnull(Lookup('Unburdened Cost Date','Unburdened Cost Date',EFFECTIVE_DATE,'UnburdenedCostDate')),0,1) as Benchmark  // Returns value of param 1 In Table (4TH Param) From the record where 2nd Param field = 3rd param field )
FROM
[..\QVD\FGCostElements.qvd]
(qvd);

Benchmark:
left join(fg_cost_elements)
Load
_s_item_key,
'Yes' as [Benchmark Exists PPV]
Resident fg_cost_elements
WHERE EXISTS ([Unburdened Cost Date],EFFECTIVE_DATE);
 
left join(fg_cost_elements)
LOAD
_fg_key,
IF(IsNull([Benchmark Exists PPV]),'No',[Benchmark Exists PPV]) as [Benchmark Exists]
Resident fg_cost_elements;

drop field [Benchmark Exists PPV];

// Getting Cost Roll Periods per Supplier/Effective Date and company
Temp_skey:
load * INLINE
[s_effdte_key,period];

Distinct_Supplier:
load
DISTINCT _s_key as s_comp_key     // supplier and company
Resident fg_cost_elements
order by _s_key;

FOR i = 0 to NoOfRows('Distinct_Supplier')-1

  Let sup=Peek('s_comp_key',$(i),'Distinct_Supplier');

  supp_cost_rolls:
  LOAD DISTINCT
   _s_effdte_key as supp_effdte_key,  // supplier,effective date, and company
   SUPPLIER_ID as SUPPLIER,
   _s_key as s_comp_key
  Resident fg_cost_elements
  WHERE _s_key = '$(sup)'
     and trim(Benchmark)='0'
  ORDER BY EFFECTIVE_DATE;

  FOR j = 0 to NoOfRows('supp_cost_rolls')-1
   LET supp_eff_key = Peek('supp_effdte_key',$(j),'supp_cost_rolls');
   LET counter = $(j)+1;
 
      Concatenate(Temp_skey)
      LOAD '$(supp_eff_key)' as s_effdte_key,'$(counter)' as period
      AutoGenerate(1);
      Let counter=0;
  next
      drop table supp_cost_rolls; 
next

Table output is attached.

[petter]

The LOAD-statement that has the ORDER BY clause is repeatedly performed for each combination of Supplier and Company and that will remix the order. So you can solve this by doing a final resident load after your last line in your load script:

final_supp_cost_rolls:

LOAD

     *

RESIDENT

     supp_cost_rolls

ORDER BY

     supp_effdte_key;

DROP TABLE supp_cost_rolls;

RENAME TABLE final_supp_cost_rolls INTO supp_cost_rolls;

[swuehl]

You can't expect a sorted output table when using DISTINCT:

Re: My "ORDER BY" on a resident table doesn't work‌

Maybe consider using a WHERE NOT EXISTS( supp_effdte_key, _s_effdte_key) clause instead of the DISTINCT LOAD prefix, if that sounds feasible.

[triciagdaly]

I am trying to mark your answer as Correct, but the only option under Actions was to mark as 'Helpful'.