topic Calculation fields in the script in QlikView

Calculation fields in the script

Sun, 25 May 2014 13:42:55 GMT

Hi,

In the below script I am pulling two tables from two DB's and wanted to create few calculated fields using some conditions from the different tables.

Like IF(CATE=67,(0-PRES),PRES) as Sales_Qty

Not sure how to achieve this, can you pls guide me.

ODBC CONNECT TO ABC (XUserId is SSOKAKBJNSeA, XPassword is LJTQNKBNJLbaWQBW);

LOAD

CATE,

NO,

CLASS,

PRES,

DESC

'A' as Source;

SQL SELECT *

FROM "Master_File";

LOAD

WMNO as NO,

WBI,

WNMTH,

WMSK,

'WH A' AS WCmpy;

SQL SELECT *

FROM "Warehouse_Item_File";

ODBC CONNECT TO DEF (XUserId is SeLbIKBJNadB, XPassword is YNbSMKBNJLbaWQZJ);

LOAD

CATE,

NO,

CLASS,

PRES,

DESC

'B' as Source;

SQL SELECT *

FROM "Master_File";

LOAD

WMNO as NO,

WBI,

WNMTH,

WMSK,

'WH B' AS WCmpy;

SQL SELECT *

FROM "Warehouse_Item_File";

Thanks,

Re: Calculation fields in the script

maxgro — Sun, 25 May 2014 14:15:10 GMT

I suppose

- at the end of your script you have 2 tables, the first one with table 1 (first db) and 3 (second db) concatenated, the second etc.....

- NO, Source is your logical key in first table

add this at the end; this code join your first table with your first table using NO and Source as join field and adds a field

left join (NameOFYourFirstTable)
LOAD
NO,
Source,
IF(CATE=67,(0-PRES),PRES) as Sales_Qty
resident
NameOFYourFirstTable;

Another one (second condition doesn't apply for this)

rename NameOFYourFirstTable to TMP;

NameOFYourFirstTable:
noconcatenate
load 
    *,
    IF(CATE=67,(0-PRES),PRES) as Sales_Qty
resident Tmp;

drop table Tmp;

Re: Calculation fields in the script

rwunderlich — Sun, 25 May 2014 15:25:46 GMT

It looks to like CATE and PRES are in the same table. If so, can't you just add the calc to the LOAD statement?

IF(CATE=67,(0-PRES),PRES) as Sales_Qty

-Rob

Re: Calculation fields in the script

Sun, 01 Jun 2014 09:14:42 GMT

Nope, typo error, if it comes from different table. Pls let me know.

Thanks,

Re: Calculation fields in the script

Tue, 03 Jun 2014 09:14:49 GMT

Can you pls let me know how to create calculated fields if it comes from two different tables.

Thanks,

Re: Calculation fields in the script

tresB — Tue, 03 Jun 2014 09:23:58 GMT

Join or ApplyMap