I am loading data from database-tables (all positions of an order) and I have to add some columns (currencys, calculations,...) . Since I want to avoid the use of one formula x times, I made it by multiple LOAD statements (see below). But with the last "load" Qlikview runs forever. If I make the whole formula for "ordersValue" in the "load"-command before and multiply it with isZFlag then it works.
So what can I do to get my data without lots of formula-copies? Do you need further informations? I'm running QV9 Personal Edition, because I have to evaluate this software.
Kind regards Peter
ODBCCONNECTTO Cognos (XUserId is xxx, XPassword is xxx);
//Position-Data relFB_Base: SQLSELECT <Some fields>, Segm2_Meng, Netto2, CASE WHEN (a + b + c) = 0 THEN Segm2_Meng ELSE (a + b + c) END as orderquantity, CASE WHEN (SAint = 215 and Mnr not in ('350', '355')) THEN 0 ELSE 1 END as isZFlag, CASE WHEN Segm2_Term is null THEN Segm1_Term ELSE Segm2_Term END as Eventtime_orig, WE, CASE WHEN WE = 'Eur' THEN 1 ELSE 0 END as isEuroFlag FROM <table1> WHERE <Some conditions>
//join data from the same table, but it is "Header"-Data, the join works correctly LEFTJOINSELECT <Some fields> FROM <table1> WHERE <Some conditions>
//join data from different table, it's been Currency-Data (WE is the join-Field) LEFTJOINSELECT Kurs, Basis, WE FROM <CurrencyTable>
//Add some columns to the base_Table relFB_Temp1: LOAD *, if (Segm2_Meng <> 0, if(isEuroFlag = 1, Netto2/Segm2_Meng*orderquantity, ((Netto2*Kurs/Basis)/Segm2_Meng)* orderquantity), NULL()) as ordersValue RESIDENT relFB_Base;
//Add 1 column with calculation LOAD *, ordersValue * isZFlag as myTestField RESIDENT relFB_Temp1;
you created a new internal table with the same data as relFB_Temp1 plus your new column (myTestField). This makes a synthetic key between all columns which are in both tables. What you could have done is name that load (re1FB_Temp2) and dropped reFB_Temp1 last.
DROP TABLE relFB_Base, reFBTemp1;
Presceding load is just a feature used for purposes like this. When you need to calculate something on a field in a load.