I am currently failing to solve the following problem:
I have a large statistics table with a plant.ID and a materials.ID as the unique key. In addition, I have a table where certain plant-material combinations are defined as exceptions with an exception text. Now I also want to make the combinations of my application selectable that are not exceptions and mark them as "normal".
As a way out I thought something like that, but there is at least one error somewhere. Maybe it's too complicated and there's a much easier way to solve it:
LOAD PLANT_MAT.ID as FOC.PLANT_MAT.ID,
FOCUS.DESCRIPTION as MATERIALS.ART
(ooxml, embedded labels, table is [ABC-FOKUSPRODUKTE]);
LOAD PLANT.ID&'.'&MATERIALS.ID as FOC.PLANT_MAT.ID
(qvd)WHERE(CUSTOMER.ID <> '123450000') and (PLANT.ID >= '1234' and PLANT.ID <= '4321');
'Normal' as MATERIALS.ART
WHERE Not Exists(FOC.PLANT_MAT.ID);
DROP Tables TMP.MATERIAL.ART.1,TMP.MATERIAL.ART.2;
A LookUp or an ApplyMap were simply too slow with the amount of data.
Many thanks in advance,
BTW ApplyMap() is an extremely fast lookup function and often beats JOINs and copies. I really can't imagine that any other solution would be faster. What amount of data are we talking about; how many 'Normal' rows, and how many 'Abnormal' ones?
As far as I can see, the most probably cause is the content of field FOC.PLANT_MAT.ID. You see, during script execution, a field (through its symbol table) combines all values present in all columns with the same name in all tables that were loaded previously. And the combination of FOC.PLANT_MAT.ID values probably spans all values, both Normal and "Abnormal". The result is that the WHERE NOT EXISTS() in the last CONCATENATE LOAD doesn't really work.
You can solve this by creating a temporary field in the NOCONCATENATE LOAD, like
FOC.PLANT_MAT.ID AS TEMP.FOC.PLANT_MAT.ID,
and now checking against this temporary field in the WHERE NOT EXISTS expression.
What about keeping the Exceptions separately?
SET OtherSymbol = +;
LOAD Key, ..., 'Y' AS IsException FROM .....
CONCATENATE (Exception) LOAD '+' AS Key, 'N' AS IsException AUTOGENERATE 1;