Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peterwh
Creator II
Creator II

Problem with adding column

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;




1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

5 Replies
Not applicable

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

peterwh
Creator II
Creator II
Author

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

prieper
Master II
Master II

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

Not applicable

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

peterwh
Creator II
Creator II
Author

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

Peter