Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
I am trying to mark your answer as Correct, but the only option under Actions was to mark as 'Helpful'.