topic SV:Problem with adding column in QlikView

Problem with adding column

peterwh — Tue, 22 Sep 2009 18:12:47 GMT

Hello,

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


ODBC CONNECT TO Cognos (XUserId is xxx, XPassword is xxx);

//Position-Data
relFB_Base:
SQL SELECT
<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
LEFT JOIN SELECT
<Some fields>
FROM <table1>
WHERE <Some conditions>
 
//join data from different table, it's been Currency-Data (WE is the join-Field)
LEFT JOIN SELECT 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;

//delete first table
DROP TABLE relFB_Base;

SV:Problem with adding column

Tue, 22 Sep 2009 21:26:34 GMT

I think your problem is that you get a large synthetic key with your "Add 1 column with calculation" load.

You could do it with a presceding load:

//Add some columns to the base_Table
relFB_Temp1:

LOAD *,
ordersValue*isZFlag as myTestField;
LOAD *,
if (Segm2_Meng <> 0,
if(isEuroFlag = 1,
Netto2/Segm2_Meng*orderquantity,
((Netto2*Kurs/Basis)/Segm2_Meng)* orderquantity), NULL())
as ordersValue
RESIDENT relFB_Base;

//delete first table
DROP TABLE relFB_Base;

/Michael

SV:Problem with adding column

peterwh — Wed, 23 Sep 2009 14:03:15 GMT

Hello Michael,

your solution works, but I don't understand why? Can you please explain it. My problem is: why can I use a fieldname before it was defined and why I don't need a "FROM" or "RESIDENT".

Thanks a lot for the solutions.

Peter

SV:Problem with adding column

prieper — Wed, 23 Sep 2009 14:25:39 GMT

The trick with the preceding loads is that actually QV works from bottom to top, thus all fields, which were previously defined can be addressed in a load-statement "above".

Peter

SV:Re: SV:Problem with adding column

Wed, 23 Sep 2009 14:31:03 GMT

Hi Peter,

When you did your:

//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.

/Michael

SV:Re: SV:Problem with adding column

peterwh — Wed, 23 Sep 2009 19:35:42 GMT

Thanks for your answers. Now it's clearer and I could use these informations to improve my script.

Peter